Thread: BUG #3519: Postgres takes the wrong query plan resulting in performance issues
BUG #3519: Postgres takes the wrong query plan resulting in performance issues
From
"Mouhamadou DIA"
Date:
The following bug has been logged online: Bug reference: 3519 Logged by: Mouhamadou DIA Email address: mdia@accovia.com PostgreSQL version: 8.2.4 Operating system: Solaris 10 and Linux Redhat 4 Description: Postgres takes the wrong query plan resulting in performance issues Details: Hello, I have a Postgres instance (version 8.1.19) 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. I read from the release notes that starting from Postgres 8.2 the optimizer supports reordering outer joins. I've migrated to Postgres 8.2.4 and run the same query. It takes now 20 seconds which is still not acceptable. PS: I did the same test using Redhat Linux 4 and I have similar times Please help Thanks
Re: BUG #3519: Postgres takes the wrong query plan resulting in performance issues
From
Heikki Linnakangas
Date:
Mouhamadou DIA wrote: > I've migrated to Postgres 8.2.4 and run the same query. It takes now 20 > seconds which is still not acceptable. To help you with that, we'd need to see the EXPLAIN ANALYZE output of the query, and the CREATE TABLE statements of the tables involved in the query and their indexes. Have you vacuumed and ANALYZEd your database recently? PS. This is not a bug. Please don't use the bug reporting form for classic performance issues. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
RE : BUG #3519: Postgres takes the wrong query plan resulting in performance issues
From
"Mouhamadou Dia"
Date:
I'm sending in attachment the output of the explain analyze command and the create table statements of tables involved inthe query. Yes, I have vacuumed and analyzed my database this morning before running the query. I'm sorry; I thought it was a bug with the optimizer, that's why I used the reporting bug Thanks for your help -----Message d'origine----- De : Heikki Linnakangas [mailto:hlinnaka@gmail.com] De la part de Heikki Linnakangas Envoyé : 6 août 2007 14:32 À : Mouhamadou Dia Cc : pgsql-bugs@postgresql.org Objet : Re: [BUGS] BUG #3519: Postgres takes the wrong query plan resulting in performance issues Mouhamadou DIA wrote: > I've migrated to Postgres 8.2.4 and run the same query. It takes now 20 > seconds which is still not acceptable. To help you with that, we'd need to see the EXPLAIN ANALYZE output of the query, and the CREATE TABLE statements of the tables involved in the query and their indexes. Have you vacuumed and ANALYZEd your database recently? PS. This is not a bug. Please don't use the bug reporting form for classic performance issues. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com