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

Re: 225 times slower

From: Pailloncy Jean-Gérard <pailloncy(at)ifrance(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: 225 times slower
Date: 2004-04-20 17:10:50
Message-ID: AC3987B4-92ED-11D8-B5ED-000A95DE2550@ifrance.com (view raw or flat)
Thread:
Lists: 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


In response to

  • at 2004-04-14 12:39:21 from Pailloncy Jean-Gérard

Responses

pgsql-performance by date

Next:From: J. Andrew RogersDate: 2004-04-20 17:17:22
Subject: Re: Wierd context-switching issue on Xeon
Previous:From: Josh BerkusDate: 2004-04-20 16:59:52
Subject: Re: Wierd context-switching issue on Xeon

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