From: Alex Turner <armtuk@gmail.com>
To: Pgsql-Performance ((E-mail)) <pgsql-performance@postgresql.org>
Subject: Confusion and Questions about blocks read
Date: Fri, 22 Sep 2006 11:34:58 -0400
Message-ID: <33c6269f0609220834x6c10db9xa40d6121825dfd14@mail.gmail.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: armtuk@gmail.com
Subject: Re: Confusion and Questions about blocks read
In-Reply-To: <33c6269f0609220834x6c10db9xa40d6121825dfd14@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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