Re: 225 times slower - Mailing list pgsql-performance

Hi,

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