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.380 rows=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.410 rows=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.13 rows=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.
