From: Jeff Janes <jeff.janes@gmail.com>
To: Prodan, Andrei <Andrei.Prodan@awinta.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 'Interesting' prepared statement slowdown on large table join
Date: Wed, 11 May 2011 12:07:07 -0700
Message-ID: <BANLkTimWsVv7DgYkur3xJvu29K3x+PDAxw@mail.gmail.com> (raw)
In-Reply-To: <D33393149C01874DB3D72FE46A1B268B0C784D@vpmail05-x.intra.ads-root.de>
References: <D33393149C01874DB3D72FE46A1B268B0C784D@vpmail05-x.intra.ads-root.de>
On Wed, May 11, 2011 at 4:08 AM, Prodan, Andrei
<Andrei.Prodan@awinta.com> wrote:
>
...
>
>
> The select is as follows:
> prepare ps(varchar,varchar,varchar) as select party.party_id from party,
> big_table where external_id = $1 and party.party_id = big_table.party_id
> and attr_name = $2 and attr_value = $3;
> PREPARE
> execute ps('13','GroupId','testshop');
>
> BAD PLAN:
> testdb=# explain analyze execute ps('13','GroupId','testshop');
> QUERY
...
> -> Index Scan using attr_name_value on big_table (cost=0.00..22.85
> rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
> Index Cond: (((attr_name)::text = ($2)::text) AND
> ((attr_value)::text = ($3)::text))
So it expects 4 rows and finds 914786, essentially the whole table.
So that is bad. But what is it thinking during the GOOD PLAN state?
A possible way to get that information is to prepare a simpler
prepared statement that omits the join to party and explain analyze it
with the same params for attr_name and attr_value. If that gives you
the full table scan rather than index scan, then you can "set
enable_seqscan=off" try to force the index scan.
Cheers,
Jeff
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jeff.janes@gmail.com, Andrei.Prodan@awinta.com
Subject: Re: 'Interesting' prepared statement slowdown on large table join
In-Reply-To: <BANLkTimWsVv7DgYkur3xJvu29K3x+PDAxw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox