Confusion and Questions about blocks read

From: "Alex Turner" <armtuk(at)gmail(dot)com>
To: "Pgsql-Performance ((E-mail))" <pgsql-performance(at)postgresql(dot)org>
Subject: Confusion and Questions about blocks read
Date: 2006-09-22 15:34:58
Message-ID: 33c6269f0609220834x6c10db9xa40d6121825dfd14@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

The query expain analyze looks like this:

click-counter=# explain analyze select count(*) as count,
to_char(date_trunc('day',c.datestamp),'DD-Mon') as day from impression c,
url u, handle h where c.url_id=u.url_id and c.handle_id=h.handle_id and
h.handle like '10000.19%' group by date_trunc('day',c.datestamp) order by
date_trunc('day',c.datestamp);

QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=530282.76..530283.04 rows=113 width=8) (actual time=
191887.059..191887.131 rows=114 loops=1)
Sort Key: date_trunc('day'::text, c.datestamp)
-> HashAggregate (cost=530276.65..530278.91 rows=113 width=8) (actual
time=191886.081..191886.509 rows=114 loops=1)
-> Hash Join (cost=128.41..518482.04 rows=2358921 width=8)
(actual time=17353.281..190568.890 rows=625212 loops=1)
Hash Cond: ("outer".handle_id = "inner".handle_id)
-> Merge Join (cost=0.00..444641.52 rows=5896746 width=12)
(actual time=34.582..183154.561 rows=5896746 loops=1)
Merge Cond: ("outer".url_id = "inner".url_id)
-> Index Scan using url_pkey on url u (cost=
0.00..106821.10 rows=692556 width=8) (actual
time=0.078..83432.380rows=692646 loops=1)
-> Index Scan using impression_url_i on impression c
(cost=0.00..262546.95 rows=5896746 width=16) (actual
time=34.473..86701.410rows=5896746 loops=1)
-> Hash (cost=123.13..123.13 rows=2115 width=4) (actual
time=40.159..40.159 rows=2706 loops=1)
-> Bitmap Heap Scan on handle h
(cost=24.69..123.13rows=2115 width=4) (actual time=
20.362..36.819 rows=2706 loops=1)
Filter: (handle ~~ '10000.19%'::text)
-> Bitmap Index Scan on handles_i (cost=
0.00..24.69 rows=2115 width=0) (actual time=20.264..20.264 rows=2706
loops=1)
Index Cond: ((handle >= '10000.19'::text)
AND (handle < '10000.1:'::text))
Total runtime: 191901.868 ms

(looks like it sped up a bit the second time I did it)

When I query relpages for the tables involved:

click-counter=# select relpages from pg_class where relname='impression';
relpages
----------
56869
(1 row)

click-counter=# select relpages from pg_class where relname='url';
relpages
----------
66027
(1 row)

click-counter=# select relpages from pg_class where relname='handle';
relpages
----------
72
(1 row)

click-counter=#

they only total 122968.

Home come the query statistics showed that 229066 blocks where read given
that all the blocks in all the tables put together only total 122968?

LOG: QUERY STATISTICS
DETAIL: ! system usage stats:
! 218.630786 elapsed 24.160000 user 13.930000 system sec
! [261.000000 user 85.610000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 65/47 [20176/99752] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 229066 read, 2 written, buffer
hit rate = 55.61%
! Local blocks: 0 read, 0 written, buffer
hit rate = 0.00%
! Direct blocks: 0 read, 0 written

Alex.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2006-09-22 16:24:35 Re: Confusion and Questions about blocks read
Previous Message Jim C. Nasby 2006-09-22 14:02:29 Re: Large tables (was: RAID 0 not as fast as