To: Bryce Nesbitt <bryce2@obviously.com>
cc: pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	"Jorge Montero" <jorge_montero@homedecorators.com>
Subject: Re: Re: 512,
	600ms query becomes 7500ms... but why? Postgres 8.3 query planner
	quirk?
In-reply-to: <4B7362DC.2060301@obviously.com> 
References: <4B723042.7090406@obviously.com> <4B726E81.2060904@obviously.com>
	<603c8f071002101231k45e9161eyeacf9072219489eb@mail.gmail.com>
	<4B7362DC.2060301@obviously.com>
Comments: In-reply-to Bryce Nesbitt <bryce2@obviously.com>
	message dated "Wed, 10 Feb 2010 17:52:28 -0800"
Date: Wed, 10 Feb 2010 22:52:49 -0500
Message-ID: <28370.1265860369@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bryce Nesbitt <bryce2@obviously.com> writes:
> The query plans are now attached (sorry I did not start there: many
> lists reject attachments). Or you can click on "text" at the query
> planner analysis site http://explain.depesz.com/s/qYq

At least some of the problem is the terrible quality of the rowcount
estimates in the IN subquery, as you extracted here:

>  Nested Loop  (cost=0.00..23393.15 rows=23 width=4) (actual time=0.077..15.637 rows=4003 loops=1)
>    ->  Index Scan using words_word on words  (cost=0.00..5.47 rows=1 width=4) (actual time=0.049..0.051 rows=1 loops=1)
>          Index Cond: ((word)::text = 'insider'::text)
>    ->  Index Scan using article_words_wc on article_words (cost=0.00..23234.38 rows=12264 width=8) (actual time=0.020..7.237 rows=4003 loops=1)
>          Index Cond: (article_words.word_key = words.word_key)
>  Total runtime: 19.776 ms

Given that it estimated 1 row out of "words" (quite correctly) and 12264
rows out of each scan on article_words, you'd think that the join size
estimate would be 12264, which would be off by "only" a factor of 3 from
the true result.  Instead it's 23, off by a factor of 200 :-(.

Running a roughly similar test case here, I see that 8.4 gives
significantly saner estimates, which I think is because of this patch:
http://archives.postgresql.org/pgsql-committers/2008-10/msg00191.php

At the time I didn't want to risk back-patching it, because there
were a lot of other changes in the same general area in 8.4.  But
it would be interesting to see what happens with your example if
you patch 8.3 similarly.  (Note: I think only the first diff hunk
is relevant to 8.3.)

			regards, tom lane