Trying to track down why a query executed from a web page
is much slower than same query executed from psql. I guess
this is more of a Perl/DBI question, but I thought there might
be more people here with answers than in a Perl group.
I have set up indexes on a table to allow a fast search with
an achored LIKE condition (e.g. " WHERE ... LIKE 'xxx%'").
When I execute a query with the WHERE parameter embedded
in the query, from psql or Perl/DBI, it runs very quickly.
SELECT g.entr FROM gloss g WHERE g.txt LIKE 'only%'; ('')
34 rows.
Exe time = 0.23 seconds
I run the same query using a "?" in the SQL text and a separate
parameter, it runs slowly (time consistent with a sequential scan).
SELECT g.entr FROM gloss g WHERE g.txt LIKE ?; ('only%')
34 rows.
Exe time = 16.31 seconds
I can understand that when the SQL statement is prepared,
the planner has no idea what the argument is (could be '%xxx')
so it can't generate an index-using plan.
Is there some way to tell Postgresql that the LIKE argument
will be achored? Or is the only option to decontaminate the
argument (since it comes from a web page) and embed it in
the SQL string (also losing the benefits of reexecuting a
prepared query)?