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