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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
