From: | "Luiz Rafael Culik Guimaraes" <culikr(at)brturbo(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | query optimizer dont treat correctly OR |
Date: | 2004-04-27 14:48:48 |
Message-ID: | 007101c42c66$c0bffb30$2b38fea9@luiz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
Hello folks
See the command bellow. I use some thing simmilar about an decade on
Oracle,
Sybase, MSSQL, DB2, etc. But with Postgresql , he generate an FULL TABLE
SCAN,
and consequenyly it take about 10 minutes to run (Very big table..)
----------------------------------------------------------------------
SELECT A.SR_RECNO , A.CDEMP, A.NRPED,A.SR_RECNO
FROM FTB01 A
WHERE ( A.CONTROLE <= ' ' AND A.CDEMP = '75' AND A.NRPED < '0000261' )
OR ( A.CONTROLE = ' ' AND A.CDEMP < '75' )
OR ( A.CONTROLE < ' ' )
ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC
LIMIT 170
----------------------------------------------------------------------
Otherwise, is i write the query on the form of an little more "dummy" and
eliminating the
"OR" and changing by UNION, the time of execution drops to less menos
of two seconds
----------------------------------------------------------------------
SELECT TMP1.* FROM (
SELECT A.SR_RECNO, A.CDEMP, A.NRPED,A.SR_RECNO
FROM FTB01 A WHERE ( A.CONTROLE <= ' ' AND A.CDEMP = '75' AND A.NRPED <
'0000261' )
ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 ) TMP1
UNION
SELECT TMP2.* FROM (
SELECT A.SR_RECNO, A.CDEMP, A.NRPED,A.SR_RECNO
FROM FTB01 A WHERE ( A.CONTROLE = ' ' AND A.CDEMP < '75' )
ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 ) TMP2
UNION
SELECT TMP3.* FROM (
SELECT A.SR_RECNO, A.CDEMP, A.NRPED,A.SR_RECNO
FROM FTB01 A WHERE OR ( A.CONTROLE < ' ' )
ORDER BY A.CDEMP DESC, A.NRPED DESC, A.SR_RECNO DESC LIMIT 170 ) TMP3
ORDER BY CDEMP DESC, NRPED DESC, SR_RECNO DESC
LIMIT 170
----------------------------------------------------------------------
The comand above works (even being 10 x slower then other Databases
) with our generate the full scan.
Why Post do this wrong julgment with the initial command?
Exist some thing that i can configure to to make postgres works correctly ?
Obs.:
* Tested on versions 7.3.2 e 7.4.1
* Obvialy the vacuumm full analyse was executed
Thanks
Luiz
From | Date | Subject | |
---|---|---|---|
Next Message | Lincoln Yeoh | 2004-04-27 15:00:52 | Re: shadowing (like IB/Firebird) |
Previous Message | Ivan Sergio Borgonovo | 2004-04-27 14:25:06 | composite type and assignment in plpgsql |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-04-27 15:35:39 | Re: [SQL] query optimizer dont treat correctly OR |
Previous Message | Richard Huxton | 2004-04-27 13:16:16 | Re: Customised Encoding |