From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | David Chapman <david(dot)chapman(at)mavensecurities(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Different query plan used for the same query depending on how parameters are passed |
Date: | 2017-05-16 14:17:11 |
Message-ID: | 2eb2bc0d-a465-88d3-9ded-f5fde5fd25b0@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Goodson | 2017-05-16 14:22:14 | Re: EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2 |
Previous Message | Adrian Klaver | 2017-05-16 14:03:54 | Re: Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9 |