From: Dario <dario_d_s@unitech.com.ar>
To: Chris Travers <chris@travelamericas.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Left joining against two empty tables makes a query
Date: Fri, 29 Jul 2005 20:18:56 -0300
Message-ID: <MHEDJHCKDNOEHJKHIOCJEEEPCFAA.dario_d_s@unitech.com.ar> (raw)
In-Reply-To: <42E9BD43.6060203@travelamericas.com>
Sorry for my english.
May I ask? (i'm still learning postgresql). Isn't outer join forcing "join
order"?
The planner will resolve a, then ac in order to resolve left join previously
and will not be able to choose the customer_id filter (more selective)...
AFAIK (not too far :-)) this will be the join order, even if projects and
deparments are not empty, no matter how much statistical info you (the
engine) have (has).
Workaround:
You should probably try to use a subquery to allow planner to choose join
order (as long as you can modify source code :-O ). You know project and
department are empty now so...
SELECT aa.accno, aa.description, aa.link, aa.category, aa.project_id,
aa.department, p.projectnumber, d.description from (
SELECT c.accno, c.description, c.link, c.category, ac.project_id,
a.department_id AS department
FROM chart c JOIN acc_trans ac ON (ac.chart_id = c.id)
JOIN ar a ON (a.id = ac.trans_id)
WHERE a.customer_id = 11373 AND a.id IN (
SELECT max(id) FROM ar WHERE customer_id = 11373)
) aa
LEFT JOIN project p ON (aa.project_id = p.id)
LEFT JOIN department d ON (d.id = aa.department)
Doubt of it. I rewrite it at first sight.
Long life, little spam and prosperity.
-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]En nombre de Chris
Travers
Enviado el: viernes, 29 de julio de 2005 2:23
Para: Gnanavel S
CC: Chris Travers; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] Left joining against two empty tables makes a
query
>
> Secondly, the project table has *never* had anything in it. So where
> are these numbers coming from?
>
>
> pg_statistics
I very much doubt that. I was unable to locate any rows in pg_statistic
where the pg_class.oid for either table matched any row's starelid.
Tom's argument that this is behavior by design makes sense. I assumed
that something like that had to be going on, otherwise there would be
nowhere for the numbers to come from. I.e. if there never were any rows
in the table, then if pg_statistic is showing 1060 rows, we have bigger
problems than a bad query plan. I hope however that eventually tables
which are truly empty can be treated intelligently sometime in the
future in Left Joins. Otherwise this limits the usefulness of out of
the box solutions which may have functionality that we don't use. Such
solutions can then kill the database performance quite easily.
Chris Travers
Metatron Technology Consulting
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dario_d_s@unitech.com.ar, chris@travelamericas.com
Subject: Re: Left joining against two empty tables makes a query
In-Reply-To: <MHEDJHCKDNOEHJKHIOCJEEEPCFAA.dario_d_s@unitech.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox