Re: WHERE IN (subselect) versus WHERE IN (1,2,3,) - Mailing list pgsql-general

From Tom Lane
Subject Re: WHERE IN (subselect) versus WHERE IN (1,2,3,)
Date
Msg-id 25285.1332167318@sss.pgh.pa.us
Whole thread Raw
In response to Re: WHERE IN (subselect) versus WHERE IN (1,2,3,)  (Kevin Goess <kgoess@bepress.com>)
Responses Re: WHERE IN (subselect) versus WHERE IN (1,2,3,)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Kevin Goess <kgoess@bepress.com> writes:
> On Mon, Mar 19, 2012 at 9:24 AM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
>> That means that your statistics are not accurate.

> Aha, thanks, that explains why my test table with one row was so bad.  But
> even with all freshly ANALYZE'd tables, I still see the query reverting to
> a sequential scan on that big contexts table once the number of rows in the
> subselect goes over 199.  Here's a simplified version that demonstrates the
> problem.

You've still got a nasty join-size estimation error:

>          ->  Nested Loop  (cost=6.18..1939.43 rows=411736 width=8) (actual
> time=0.203..3.487 rows=35 loops=1)

It's not apparent why that's so far off ...

            regards, tom lane

pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: Anonymized database dumps
Next
From: Janning Vygen
Date:
Subject: Re: Anonymized database dumps