On Thu, Aug 1, 2013 at 7:22 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
> On Thu, Aug 1, 2013 at 5:44 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>> Scott Marlowe <scott.marlowe@xxxxxxxxx> 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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
