Re: Different query plan used for the same query depending on how parameters are passed

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David Chapman
Sent: Tuesday, May 16, 2017 9:02 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Different query plan used for the same query depending on how parameters are passed

Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.

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.

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?

It’s because when optimizer builds execution plan for parametrized queiry, it doesn’t know what values for t1 and t2 will be provided for WHERE clause.

Regards,

Igor Neyman