From: | Donald Dong <xdong(at)csumb(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Why could GEQO produce plans with lower costs than the standard_join_search? |
Date: | 2019-05-22 19:53:56 |
Message-ID: | C9891149-A4E9-4B4F-855B-CDE0641D3BEA@csumb.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On May 22, 2019, at 11:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Donald Dong <xdong(at)csumb(dot)edu> writes:
>> I find the cost from cheapest_total_path->total_cost is different
>> from the cost from queryDesc->planstate->total_cost. What I saw was
>> that GEQO tends to form paths with lower
>> cheapest_total_path->total_cost (aka the fitness of the children).
>> However, standard_join_search is more likely to produce a lower
>> queryDesc->planstate->total_cost, which is the cost we get using
>> explain.
>
>> I wonder why those two total costs are different? If the total_cost
>> from the planstate is more accurate, could we use that instead as the
>> fitness in geqo_eval?
>
> You're still asking us to answer hypothetical questions unsupported
> by evidence. In what case does that really happen?
Hi,
My apologies if this is not the minimal necessary set up. But here's
more information about what I saw using the following query
(JOB/1a.sql):
SELECT MIN(mc.note) AS production_note,
MIN(t.title) AS movie_title,
MIN(t.production_year) AS movie_year
FROM company_type AS ct,
info_type AS it,
movie_companies AS mc,
movie_info_idx AS mi_idx,
title AS t
WHERE ct.kind = 'production companies'
AND it.info = 'top 250 rank'
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
AND (mc.note LIKE '%(co-production)%'
OR mc.note LIKE '%(presents)%')
AND ct.id = mc.company_type_id
AND t.id = mc.movie_id
AND t.id = mi_idx.movie_id
AND mc.movie_id = mi_idx.movie_id
AND it.id = mi_idx.info_type_id;
I attached the query plan and debug_print_rel output for GEQO and
standard_join_search.
planstate->total_cost cheapest_total_path
GEQO 54190.13 54239.03
STD 54179.02 54273.73
Here I observe GEQO produces a lower
cheapest_total_path->total_cost, but its planstate->total_cost is higher
than what standard_join_search produces.
Regards,
Donald Dong
Attachment | Content-Type | Size |
---|---|---|
geqo_query_plan.txt | text/plain | 2.1 KB |
std_query_plan.txt | text/plain | 2.1 KB |
std_debug_print.txt | text/plain | 1006 bytes |
geqo_debug_print.txt | text/plain | 1.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-05-22 19:55:50 | Re: FullTransactionId changes are causing portability issues |
Previous Message | Andres Freund | 2019-05-22 19:44:38 | Re: PostgreSQL 12 Beta 1 press release draft |