On 05/16/2017 06:01 AM, David Chapman wrote:
I have a table that includes two text columns t1 and t2, and a composite
index on these columns. When issuing a query of the following form:
SELECT * FROM test WHERE t1 = 'X' and t2 = ANY(ARRAY['Y1', 'Y2', ..])
I have observed that it will use the index and have reasonable
performance if the whole query is passed as a single big string. However
if it is parameterised (I'm using Npgsql) it switches to doing a
sequence scan and performs terribly.
What Postgres version?
Can you show the parametrized version?
What is the output if you add EXPLAIN ANALYZE to the beginning of the
query?:
https://www.postgresql.org/docs/9.6/static/sql-explain.html
The table contains approx 2.3 million records and the query matches
about 20k records (i.e. there are 20k 'Y' values in the array).
I have experimented with changing work_mem, preparing the statement in
advance, ANALYZEing the table, none of these change the behavior.
Why does the query planner choose to ignore the index when the command
is parameterised?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
