From: | "Stuart" <smcg2297(at)frii(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | dbi and indexes |
Date: | 2007-08-06 19:30:41 |
Message-ID: | f97st3$adp$1@sea.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)?
From | Date | Subject | |
---|---|---|---|
Next Message | Lewis Cunningham | 2007-08-06 19:31:09 | Re: Procedural Code Profiling |
Previous Message | korry.douglas | 2007-08-06 19:26:43 | Re: Procedural Code Profiling |