Postgres optimizer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 

 

 

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux