pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: 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