Re: [GENERAL] Different query plan used for the same query dependingon how parameters are passed

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@aklaver.com