| From: | Peter Geoghegan <pg(at)bowt(dot)ie> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> |
| Subject: | Re: TPC-H Q20 from 1 hour to 19 hours! |
| Date: | 2017-06-11 22:31:55 |
| Message-ID: | CAH2-Wzk0P8QrLQRL_y-huBKNoviDem4qv-2qisqYQcFLSfJuAQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Sun, Jun 11, 2017 at 10:27 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> Note that I introduced a new, redundant exists() in the agg_lineitem
> fact table subquery. It now takes 23 seconds for me on Tomas' 10GB
> TPC-H dataset, whereas the original query took over 90 minutes.
> Clearly we're missing a trick or two here. I think that you need a
> DAG-shaped query plan to make this work well, though, so it is
> certainly a big project.
On closer examination, this seems to be due to the number of heap
accesses required by an index-only scan that the original query plan
uses; my test case was invalid. All the same, I understand that moving
predicates into many (TPC-H) subqueries is an important optimization,
and hope that more work can be done in this area.
--
Peter Geoghegan
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tomas Vondra | 2017-06-11 23:10:31 | Re: TPC-H Q20 from 1 hour to 19 hours! |
| Previous Message | Andrew Gierth | 2017-06-11 22:02:17 | Re: transition table behavior with inheritance appears broken |