Skip site navigation (1) Skip section navigation (2)

query optimizer dont treat correctly OR

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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



Responses

pgsql-sql by date

Next:From: Richard HuxtonDate: 2004-04-27 15:35:39
Subject: Re: [SQL] query optimizer dont treat correctly OR
Previous:From: Richard HuxtonDate: 2004-04-27 13:16:16
Subject: Re: Customised Encoding

pgsql-general by date

Next:From: Lincoln YeohDate: 2004-04-27 15:00:52
Subject: Re: shadowing (like IB/Firebird)
Previous:From: Ivan Sergio BorgonovoDate: 2004-04-27 14:25:06
Subject: composite type and assignment in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group