dbi and indexes

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)?

Browse pgsql-general by date

  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