The same query, two different methods, significantly different performance

Question

I have a Postgres table with over 8 million rows. Given the following two ways to execute the same query through DBD::Pg , I get completely different results.

 $q .= '%'; ## query 1 my $sql = qq{ SELECT a, b, c FROM t WHERE Lower( a ) LIKE '$q' }; my $sth1 = $dbh->prepare($sql); $sth1->execute(); ## query 2 my $sth2 = $dbh->prepare(qq{ SELECT a, b, c FROM t WHERE Lower( a ) LIKE ? }); $sth2->execute($q); 

query 2 is at least an order of magnitude slower than query 1 ... it seems that it does not use indexes, while query 1 uses an index.

I would like to hear why.

Answer 1

For LIKE expressions, b-tree indexes can only be used if the search pattern is left, that is, completed with % . More details in the manual .
_{Thanks to @evil otto for the link. This link is for the current version. Sub>}

The first query provides this important information during preparation, so the query planner can use the appropriate index.

The second query does not provide any template information during preparation, so the query planner cannot use any indexes.

Answer 2

I suspect that in the first case, the query compiler / optimizer detects that the sentence is constant and can build an optimal query plan. In the second, it should compile a more general query, because the bound variable may be something at runtime.

Answer 3

Are you using both test cases from the same file using the same $ dbh object? I think that the reason for the increase in speed in the second case is because you are using a prepared statement that has already been parsed (but maybe I'm wrong :)).

Answer 4

And, I see, I will leave after this comment, since I do not know Perl. But I would believe that the editor is right by highlighting $ q as a constant. I assume that you need to concatenate the value into a string, and not just refer to the variable. So, I assume that if + is used to concatenate strings in perl, then use something like:

my $ sql = qq {SELECT a, b, c FROM t WHERE Lower (a) LIKE '} + $ q + qq {'};

(Note: If the language is not tightly integrated with a database, such as Oracle / PLSQL, you usually need to create a fully valid SQL string before sending it to the database, rather than expecting the compiler to “interpolate” / “Replace” the value of the variable .)

I would again suggest that you get COUNT () from the instructions to make sure that you are comparing an apple with apples.

Answer 5

I don't know Postgres at all, but I think that on line 7 (WHERE Lower (a) LIKE '$ q'), $ q is actually a constant. It looks like your editor also thinks so, as it is highlighted in red. You probably still need to use? for a variable.

To check, do COUNT (*) and make sure they match - I could be outside the base.