Re: 'Interesting' prepared statement slowdown on large table join

On Wed, May 11, 2011 at 4:08 AM, Prodan, Andrei
<Andrei(dot)Prodan(at)awinta(dot)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