pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 225 times slower
3+ messages / 2 participants
[nested] [flat]

* Re: 225 times slower
@ 2004-04-20 17:10 Pailloncy Jean-Gérard <pailloncy@ifrance.com>
  2004-04-22 04:15 ` Re: 225 times slower Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Pailloncy Jean-Gérard @ 2004-04-20 17:10 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: 225 times slower
  2004-04-20 17:10 Re: 225 times slower Pailloncy Jean-Gérard <pailloncy@ifrance.com>
@ 2004-04-22 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2004-04-22 04:15 UTC (permalink / raw)
  To: Pailloncy Jean-Gérard <pailloncy@ifrance.com>; +Cc: pgsql-performance

=?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= <pailloncy@ifrance.com> writes:
> 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

The planner is guessing that scanning in rec_id order will produce a
matching row fairly quickly (sooner than selecting all the matching rows
and sorting them would do).  It's wrong in this case, but I'm not sure
it could do better without very detailed cross-column statistics.  Am I
right to guess that the rows that match the WHERE clause are not evenly
distributed in the rec_id order, but rather there are no such rows till
you get well up in the ordering?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: 225 times slower
@ 2004-04-22 18:46 Pailloncy Jean-Gérard <pailloncy@ifrance.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Pailloncy Jean-Gérard @ 2004-04-22 18:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>

> The planner is guessing that scanning in rec_id order will produce a
> matching row fairly quickly (sooner than selecting all the matching 
> rows
> and sorting them would do).  It's wrong in this case, but I'm not sure
> it could do better without very detailed cross-column statistics.

> Am I
> right to guess that the rows that match the WHERE clause are not evenly
> distributed in the rec_id order, but rather there are no such rows till
> you get well up in the ordering?

I must agree that the data are not evenly distributed....

For table url:
count         271.395
min rec_id          1
max rec_id  3.386.962

dps=> select * from url where crc32=419903683;
count             852
min rec_id    264.374
max rec_id  2.392.046

I do
dps=> select ctid, rec_id from url where crc32=419903683 order by 
crc32,rec_id;
And then in a text edit extract the "page_id" from ctid
and there is 409 distinct pages for the 852 rows.
There is 4592 pages for the tables url.

dps=> select (rec_id/25), count(*) from url where crc32=419903683 group 
by rec_id/25 having count(*)>4 order by count(*) desc;
  ?column? | count
----------+-------
     30289 |    25
     11875 |    24
     11874 |    24
     11876 |    24
     28154 |    23
     26164 |    21
     26163 |    21
     55736 |    21
     40410 |    20
     47459 |    20
     30290 |    20
     28152 |    20
     26162 |    19
     30291 |    19
     37226 |    19
     60357 |    18
     28150 |    18
     12723 |    17
     40413 |    17
     40412 |    16
     33167 |    15
     40415 |    15
     12961 |    15
     40414 |    15
     28151 |    14
     63961 |    14
     26165 |    13
     11873 |    13
     63960 |    12
     37225 |    12
     37224 |    12
     20088 |    11
     30288 |    11
     91450 |    11
     20087 |    11
     26892 |    10
     47458 |    10
     40411 |    10
     91451 |    10
     12722 |    10
     28153 |     9
     43488 |     9
     60358 |     7
     60356 |     7
     11877 |     7
     33168 |     6
     91448 |     6
     26161 |     6
     40409 |     5
     28155 |     5
     28318 |     5
     30292 |     5
     26891 |     5
     95666 |     5
(54 rows)



An other question, with VACUUM VERBOSE ANALYZE, I see:
> INFO:  "url": removed 568107 row versions in 4592 pages
> DETAIL:  CPU 0.51s/1.17u sec elapsed 174.74 sec.
And I run pg_autovacuum.
Does the big number (568107) of removed row indicates I should set a 
higher max_fsm_pages ?

 > grep fsm /var/pgsql/postgresql.conf
max_fsm_pages = 60000           # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 200         # min 100, ~50 bytes each

dps=> VACUUM VERBOSE ANALYSE url;
INFO:  vacuuming "public.url"
INFO:  index "url_crc" now contains 211851 row versions in 218 pages
DETAIL:  129292 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/1.38u sec elapsed 5.71 sec.
INFO:  index "url_seed" now contains 272286 row versions in 644 pages
DETAIL:  568107 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.10s/2.96u sec elapsed 13.06 sec.
INFO:  index "url_referrer" now contains 272292 row versions in 603 
pages
DETAIL:  568107 index row versions were removed.
4 index pages have been deleted, 0 are currently reusable.
CPU 0.10s/2.98u sec elapsed 22.30 sec.
INFO:  index "url_next_index_time" now contains 272292 row versions in 
684 pages
DETAIL:  568107 index row versions were removed.
42 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/1.80u sec elapsed 9.50 sec.
INFO:  index "url_status" now contains 272298 row versions in 638 pages
DETAIL:  568107 index row versions were removed.
12 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/2.18u sec elapsed 13.66 sec.
INFO:  index "url_bad_since_time" now contains 272317 row versions in 
611 pages
DETAIL:  568107 index row versions were removed.
4 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/2.40u sec elapsed 10.99 sec.
INFO:  index "url_hops" now contains 272317 row versions in 637 pages
DETAIL:  568107 index row versions were removed.
5 index pages have been deleted, 0 are currently reusable.
CPU 0.04s/2.24u sec elapsed 12.46 sec.
INFO:  index "url_siteid" now contains 272321 row versions in 653 pages
DETAIL:  568107 index row versions were removed.
13 index pages have been deleted, 0 are currently reusable.
CPU 0.14s/2.05u sec elapsed 11.63 sec.
INFO:  index "url_serverid" now contains 272321 row versions in 654 
pages
DETAIL:  568107 index row versions were removed.
8 index pages have been deleted, 0 are currently reusable.
CPU 0.10s/2.27u sec elapsed 11.45 sec.
INFO:  index "url_url" now contains 272065 row versions in 1892 pages
DETAIL:  193884 index row versions were removed.
5 index pages have been deleted, 0 are currently reusable.
CPU 0.39s/1.50u sec elapsed 36.99 sec.
INFO:  index "url_last_mod_time" now contains 272071 row versions in 
317 pages
DETAIL:  193884 index row versions were removed.
7 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/1.38u sec elapsed 5.61 sec.
INFO:  index "url_pkey" now contains 272086 row versions in 328 pages
DETAIL:  193884 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/1.60u sec elapsed 60.64 sec.
INFO:  "url": removed 568107 row versions in 4592 pages
DETAIL:  CPU 0.51s/1.17u sec elapsed 174.74 sec.
INFO:  "url": found 568107 removable, 272027 nonremovable row versions 
in 4614 pages
DETAIL:  402 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.98s/26.08u sec elapsed 466.27 sec.
INFO:  vacuuming "pg_toast.pg_toast_137628026"
INFO:  index "pg_toast_137628026_index" now contains 0 row versions in 
1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  "pg_toast_137628026": found 0 removable, 0 nonremovable row 
versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO:  analyzing "public.url"
INFO:  "url": 4624 pages, 150000 rows sampled, 577419 estimated total 
rows
VACUUM

Cordialement,
Jean-Gérard Pailloncy




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-04-22 18:46 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-20 17:10 Re: 225 times slower Pailloncy Jean-Gérard <pailloncy@ifrance.com>
2004-04-22 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-22 18:46 Re: 225 times slower Pailloncy Jean-Gérard <pailloncy@ifrance.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