Re: subselect requires offset 0 for good performance.

On Thu, Aug 1, 2013 at 7:22 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Aug 1, 2013 at 5:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>>> I am running 8.4.15 and can try 8.4.17 if some patch has been applied
>>> to it to address this issue. I just want to know should I
>>
>>> A: upgrade to 8.4.17
>>> or
>>> B: create a self contained test case.
>>
>> A quick look at the release notes shows no planner fixes in 8.4.16 or
>> 8.4.17, so it would be rather surprising if (A) helps.
>
> OK. I was doing some initial testing and if I select out the 4 columns
> into a test table the query runs fast. If I select all the columns
> into a test table it runs slow, so it appears table width affects
> this. Will have more to report tomorrow on it.

Here's the query:
SELECT * FROM dba.pp_test_wide p LEFT JOIN
(
SELECT tree_sortkey FROM dba.pp_test_wide
WHERE tree_sortkey BETWEEN '00000000000101010000010001010100'::VARBIT
AND public.tree_right('00000000000101010000010001010100'::VARBIT)
AND product_name IS NOT NULL AND tree_sortkey <>
'00000000000101010000010001010100'::VARBIT
) pp
ON p.tree_sortkey BETWEEN pp.tree_sortkey AND public.tree_right(pp.tree_sortkey)
WHERE
p.tree_sortkey BETWEEN '00000000000101010000010001010100'::VARBIT
AND public.tree_right('00000000000101010000010001010100'::VARBIT)
AND p.tree_sortkey BETWEEN '00000000'::VARBIT AND
public.tree_right('00000000'::VARBIT)
AND p.deleted_at IS NULL
AND pp.tree_sortkey IS NULL

I extracted all the data like so:

select * into dba.pp_test_wide from original table;

and get this query plan from explain analyze:
http://explain.depesz.com/s/EPx which takes 20 minutes to run.

If I extract it this way:

select tree_sortkey, product_name, deleted_at into db.pp_test_3col
from original table;

I get this plan: http://explain.depesz.com/s/gru which gets a
materialize in it, and suddenly takes 106 ms.

the factor in performance increase is therefore ~ 11,342. that's
pretty huge. I'll try to make a self contained test case now.
Hopefully that at least points in the right direction tho to a bug of
some kind.