Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group