From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | 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 23:10:31 |
Message-ID: | 4f27a3ff-fea1-8c50-4077-b051c8c9053d@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 6/11/17 7:54 PM, Peter Geoghegan wrote:
> On Sun, Jun 11, 2017 at 10:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Do you mean teaching the optimizer to do something like this?:
>>
>> Uh, no. I don't think we want to add any run-time checks. The point in
>> this example is that we'd get a better rowcount estimate if we noticed
>> that the FK constraint could be considered while estimating the size of
>> the partsupp-to-aggregated-subquery join.
>
> Sorry for not considering the context of the thread more carefully.
> Robert said something about selectivity estimation and TPC-H to me,
> which I decide to research; I then rediscovered this thread.
>
> Clearly Q20 is designed to reward systems that do better with moving
> predicates into subqueries, as opposed to systems with better
> selectivity estimation.
>
I do strongly recommend reading this paper analyzing choke points of
individual TPC-H queries:
http://oai.cwi.nl/oai/asset/21424/21424B.pdf
It's slightly orthogonal to the issue at hand (poor estimate in Q20
causing choice of inefficient plan), it's a great paper to read. I
thought I've already posted a link to the this paper sometime in the
past, but I don't see it in the archives.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2017-06-11 23:39:40 | Re: TPC-H Q20 from 1 hour to 19 hours! |
Previous Message | Peter Geoghegan | 2017-06-11 22:31:55 | Re: TPC-H Q20 from 1 hour to 19 hours! |