Skip site navigation (1) Skip section navigation (2)

Re: plan time of MASSIVE partitioning ...

From: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: plan time of MASSIVE partitioning ...
Date: 2010-10-19 13:32:12
Message-ID: 4CBD9DDC.4040304@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

attached is a WIP patch against 9.1 current GIT that converts
eq_classes and canon_pathkeys in PlannerInfo.

Also attached is the test case again the slow query is:

explain select * from inh_parent
where timestamp1 between '2010-04-06' and '2010-06-25'
order by timestamp2;

There is intentionally no data, the planning time is slow.
The currect GIT version plans this query in 2.4 seconds,
the patched version does it in 0.59 seconds according to
gprof. The gprof outputs are also attached.

There is one problem with the patch, it doesn't survive
"make check". One of the regression tests fails the
    Assert(!cur_em->em_is_child);
line in process_equivalence() in equivclass.c, but I couldn't
yet find it what causes it. The "why" is vaguely clear:
something modifies the ec_members list in the eq_classes'
tree nodes while the node is in the tree. Because I didn't find
the offender yet, I couldn't fix it, so I send this patch as is.
I'll try to fix it if someone doesn't beat me in fixing it. :)

The query produces the same EXPLAIN output for both the
stock and the patched version, they were checked with diff.
I didn't attach it to this mail because of the size constraints.
Almost all files are compressed because of this.

Best regards,
Zoltán Böszörményi

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
     http://www.postgresql.at/


Attachment: patched-gmon.log.gz
Description: application/x-tar (112.2 KB)
Attachment: stock-gmon.log.gz
Description: application/x-tar (113.6 KB)
Attachment: childtables.sql.gz
Description: application/x-tar (18.1 KB)
Attachment: create_table.sql
Description: text/plain (210 bytes)
Attachment: 9.1-planner-speedup.patch.gz
Description: application/x-tar (20.9 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-10-19 13:47:31
Subject: Re: Extensions, this time with a patch
Previous:From: KaiGai KoheiDate: 2010-10-19 13:22:13
Subject: Re: leaky views, yet again

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group