I apologize for the mistake.
So, I dump the database, I reload it then VACUUM ANALYZE.
For each statement: I then quit postgres, start it, execute one command, then quit.
Le 14 avr. 04, à 14:39, Pailloncy Jean-Gérard a écrit :
dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND crc32=764518963 AND status IN (200,304,206) ORDER BY rec_id LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------ --------------------------------------------------------
Limit (cost=169.79..169.79 rows=1 width=4) (actual time=502.397..502.398 rows=1 loops=1)
-> Sort (cost=169.79..169.86 rows=30 width=4) (actual time=502.393..502.393 rows=1 loops=1)
Sort Key: rec_id
-> Index Scan using url_crc on url (cost=0.00..169.05 rows=30 width=4) (actual time=43.545..490.895 rows=56 loops=1)
Index Cond: (crc32 = 764518963)
Filter: ((crc32 <> 0) AND ((status = 200) OR (status = 304) OR (status = 206)))
Total runtime: 502.520 ms
(7 rows)
dps=# \q
dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND crc32=764518963 AND status IN (200,304,206) ORDER BY crc32,rec_id LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------ -----------------------------------------------------
Limit (cost=169.79..169.79 rows=1 width=8) (actual time=5.893..5.894 rows=1 loops=1)
-> Sort (cost=169.79..169.86 rows=30 width=8) (actual time=5.889..5.889 rows=1 loops=1)
Sort Key: crc32, rec_id
-> Index Scan using url_crc on url (cost=0.00..169.05 rows=30 width=8) (actual time=0.445..5.430 rows=56 loops=1)
Index Cond: (crc32 = 764518963)
Filter: ((crc32 <> 0) AND ((status = 200) OR (status = 304) OR (status = 206)))
Total runtime: 6.020 ms
(7 rows)
dps=# \q
dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND crc32=419903683 AND status IN (200,304,206) ORDER BY rec_id LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------ ----------------------------------------------------------
Limit (cost=0.00..27.95 rows=1 width=4) (actual time=11021.875..11021.876 rows=1 loops=1)
-> Index Scan using url_pkey on url (cost=0.00..11625.49 rows=416 width=4) (actual time=11021.868..11021.868 rows=1 loops=1)
Filter: ((crc32 <> 0) AND (crc32 = 419903683) AND ((status = 200) OR (status = 304) OR (status = 206)))
Total runtime: 11021.986 ms
(4 rows)
dps=# \q
dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND crc32=419903683 AND status IN (200,304,206) ORDER BY crc32,rec_id LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------ ---------------------------------------------------------
Limit (cost=2000.41..2000.41 rows=1 width=8) (actual time=48.503..48.504 rows=1 loops=1)
-> Sort (cost=2000.41..2001.45 rows=416 width=8) (actual time=48.499..48.499 rows=1 loops=1)
Sort Key: crc32, rec_id
-> Index Scan using url_crc on url (cost=0.00..1982.31 rows=416 width=8) (actual time=4.848..45.452 rows=796 loops=1)
Index Cond: (crc32 = 419903683)
Filter: ((crc32 <> 0) AND ((status = 200) OR (status = 304) OR (status = 206)))
Total runtime: 48.656 ms
(7 rows)
dps=# \q
So, with all fresh data, everything rebuild from scratch, on a backend that will done one and only one query, the results is strange.
Why adding an ORDER BY clause on a column with one value speed up the stuff 502ms to 6ms ?
Why when crc32=419903683, which is one of the most often used value in the table, the query planner chose a plan so bad (225 times slower) ?
Cordialement, Jean-Gérard Pailloncy
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
