pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
query optimizer dont treat correctly OR 2+ messages / 2 participants [nested] [flat]
* query optimizer dont treat correctly OR @ 2004-04-27 14:48 Luiz Rafael Culik Guimaraes <culikr@brturbo.com> 0 siblings, 1 reply; 2+ messages in thread From: Luiz Rafael Culik Guimaraes @ 2004-04-27 14:48 UTC (permalink / raw) To: pgsql-sql; +Cc: pgsql-general@postgresql.org 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] query optimizer dont treat correctly OR @ 2004-04-27 15:35 Richard Huxton <dev@archonet.com> parent: Luiz Rafael Culik Guimaraes <culikr@brturbo.com> 0 siblings, 0 replies; 2+ messages in thread From: Richard Huxton @ 2004-04-27 15:35 UTC (permalink / raw) To: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>; pgsql-sql; +Cc: pgsql-general@postgresql.org On Tuesday 27 April 2004 15:48, Luiz Rafael Culik Guimaraes wrote: > 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..) > Why Post do this wrong julgment with the initial command? > Exist some thing that i can configure to to make postgres works correctly ? You'll need to post the output of EXPLAIN ANALYSE for your query. This will show how many rows PG thinks it will get back compared to how many it actually gets back. -- Richard Huxton Archonet Ltd ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-27 15:35 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-04-27 14:48 query optimizer dont treat correctly OR Luiz Rafael Culik Guimaraes <culikr@brturbo.com> 2004-04-27 15:35 ` Richard Huxton <dev@archonet.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