pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres optimizer
2+ messages / 2 participants
[nested] [flat]
* Postgres optimizer
@ 2007-08-03 17:58 Mouhamadou Dia <MDia@accovia.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mouhamadou Dia @ 2007-08-03 17:58 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have a Postgres instance (version 8.1) running on a Solaris 10
machine. When I run the following query
SELECT * FROM PROR_ORG, ( ( ( ( (PRPT_PRT LEFT OUTER JOIN PRPT_PRTADR
ON
PRPT_PRT.PRT_NRI = PRPT_PRTADR.PRT_NRI AND PRPT_PRTADR.ADR_F_DEF=true)
LEFT OUTER JOIN PLGE_CTY ON PRPT_PRTADR.CTY_NRI = PLGE_CTY.CTY_NRI)
LEFT
OUTER JOIN PLGE_CTY1 PLGE_CTY_PLGE_CTY1 ON PLGE_CTY.CTY_NRI =
PLGE_CTY_PLGE_CTY1.CTY_NRI AND PLGE_CTY_PLGE_CTY1.LNG_CD = 'fr') LEFT
OUTER JOIN PLGE_CTRSD ON PRPT_PRTADR.CTRSD_CD = PLGE_CTRSD.CTRSD_CD
AND PRPT_PRTADR.CTR_ISO_CD = PLGE_CTRSD.CTR_ISO_CD) LEFT OUTER JOIN
PLGE_CTR ON PRPT_PRTADR.CTR_ISO_CD = PLGE_CTR.CTR_ISO_CD) , PROR_ORG1
PROR_ORG_PROR_ORG1, PROR_ORGT, PROR_ORGT1 PROR_ORGT_PROR_ORGT1
WHERE ( (PROR_ORG.ORGT_CD = PROR_ORGT.ORGT_CD) AND
(PROR_ORGT.ORGT_CD = PROR_ORGT_PROR_ORGT1.ORGT_CD AND
PROR_ORGT_PROR_ORGT1.LNG_CD = 'fr') AND (PROR_ORG.PRT_NRI =
PROR_ORG_PROR_ORG1.PRT_NRI AND PROR_ORG_PROR_ORG1.LNG_CD = 'fr') AND
(PROR_ORG.PRT_NRI = PRPT_PRT.PRT_NRI) ) AND ( ((PROR_ORG.ORGT_CD
='CHAIN')) )
it takes 45 seconds to run. In this case the optimizer does a sequential
scan of the PRPT_PRT table (which is the largest one) despite the
existence of an index on PRT_NRI column of PRPT_PRT table.
I've activated the GEQO but it still takes nearly the same time to run
(between 40 and 45s).
When I change the order of PRPT_PRT and PROR_ORG tables, it takes only
30 milliseconds to run. In this case the optimizer uses the index on
PRT_NRI column of PRPT_PRT table, what is normal and what I was
expecting.
Is there a known problem with the Postgres optimizer?
For your information, the same query takes 20 milliseconds to run on
Informix and 60 milliseconds to run on Oracle independently of the order
of the tables in the query.
PRPT_PRT has 1.3 millions rows
PRPT_PRTADR has 300.000 rows
PROR_ORG has 1500 rows
These are the largest tables, all the others are small tables. All
statistics are up to date.
Thanks
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgres optimizer
@ 2007-08-03 18:28 Mark Lewis <mark.lewis@mir3.com>
parent: Mouhamadou Dia <MDia@accovia.com>
0 siblings, 0 replies; 2+ messages in thread
From: Mark Lewis @ 2007-08-03 18:28 UTC (permalink / raw)
To: Mouhamadou Dia <MDia@accovia.com>; +Cc: pgsql-performance
On Fri, 2007-08-03 at 13:58 -0400, Mouhamadou Dia wrote:
> Hello,
>
> I have a Postgres instance (version 8.1) running on a Solaris 10
> machine. When I run the following query
>
>
>
> SELECT * FROM PROR_ORG, ( ( ( ( (PRPT_PRT LEFT OUTER JOIN
> PRPT_PRTADR ON
>
> PRPT_PRT.PRT_NRI = PRPT_PRTADR.PRT_NRI AND
> PRPT_PRTADR.ADR_F_DEF=true)
>
> LEFT OUTER JOIN PLGE_CTY ON PRPT_PRTADR.CTY_NRI = PLGE_CTY.CTY_NRI)
> LEFT
>
> OUTER JOIN PLGE_CTY1 PLGE_CTY_PLGE_CTY1 ON PLGE_CTY.CTY_NRI =
>
> PLGE_CTY_PLGE_CTY1.CTY_NRI AND PLGE_CTY_PLGE_CTY1.LNG_CD = 'fr')
> LEFT
>
> OUTER JOIN PLGE_CTRSD ON PRPT_PRTADR.CTRSD_CD = PLGE_CTRSD.CTRSD_CD
>
> AND PRPT_PRTADR.CTR_ISO_CD = PLGE_CTRSD.CTR_ISO_CD) LEFT OUTER JOIN
>
> PLGE_CTR ON PRPT_PRTADR.CTR_ISO_CD = PLGE_CTR.CTR_ISO_CD) , PROR_ORG1
>
> PROR_ORG_PROR_ORG1, PROR_ORGT, PROR_ORGT1 PROR_ORGT_PROR_ORGT1
>
> WHERE ( (PROR_ORG.ORGT_CD = PROR_ORGT.ORGT_CD) AND
>
> (PROR_ORGT.ORGT_CD = PROR_ORGT_PROR_ORGT1.ORGT_CD AND
>
> PROR_ORGT_PROR_ORGT1.LNG_CD = 'fr') AND (PROR_ORG.PRT_NRI =
>
> PROR_ORG_PROR_ORG1.PRT_NRI AND PROR_ORG_PROR_ORG1.LNG_CD = 'fr') AND
>
> (PROR_ORG.PRT_NRI = PRPT_PRT.PRT_NRI) ) AND ( ((PROR_ORG.ORGT_CD
> ='CHAIN')) )
>
>
>
> it takes 45 seconds to run. In this case the optimizer does a
> sequential scan of the PRPT_PRT table (which is the largest one)
> despite the existence of an index on PRT_NRI column of PRPT_PRT table.
>
> I’ve activated the GEQO but it still takes nearly the same time to
> run (between 40 and 45s).
>
> When I change the order of PRPT_PRT and PROR_ORG tables, it takes only
> 30 milliseconds to run. In this case the optimizer uses the index on
> PRT_NRI column of PRPT_PRT table, what is normal and what I was
> expecting.
>
> Is there a known problem with the Postgres optimizer?
>
> For your information, the same query takes 20 milliseconds to run on
> Informix and 60 milliseconds to run on Oracle independently of the
> order of the tables in the query.
>
>
>
> PRPT_PRT has 1.3 millions rows
>
> PRPT_PRTADR has 300.000 rows
>
> PROR_ORG has 1500 rows
>
> These are the largest tables, all the others are small tables. All
> statistics are up to date.
If I recall correctly, PG 8.2 was the first version where the planner
supported reordering outer joins. Prior releases would get poor
performance unless the joins were listed in the right order.
So it is quite possible that upgrading to 8.2 would solve your problem.
Do you have the ability to try that?
-- Mark Lewis
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-08-03 18:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-03 17:58 Postgres optimizer Mouhamadou Dia <MDia@accovia.com>
2007-08-03 18:28 ` Mark Lewis <mark.lewis@mir3.com>
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