Confusion and Questions about blocks read - Mailing list pgsql-performance

From Alex Turner
Subject Confusion and Questions about blocks read
Date
Msg-id 33c6269f0609220834x6c10db9xa40d6121825dfd14@mail.gmail.com
Whole thread Raw
Responses Re: Confusion and Questions about blocks read
List 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.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.

pgsql-performance by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Large tables (was: RAID 0 not as fast as
Next
From: Tom Lane
Date:
Subject: Re: Confusion and Questions about blocks read