Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UB5H8-0003ZA-Gs for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2013 15:20:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UB5H7-0007Av-So for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2013 15:20:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UB5H6-0007Ai-E9 for pgsql-performance@postgresql.org; Thu, 28 Feb 2013 15:20:00 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UB5Gx-0002x1-7G for pgsql-performance@postgresql.org; Thu, 28 Feb 2013 15:19:57 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApMBADd1L1GkD30E/2dsb2JhbAANOMIsgRCDEgEBAQQnURELGAkWAQ4JAwIBAgFFEwgBAbZ2ikmIP48bg0ADnCSOEA Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP/TLS/DHE-RSA-CAMELLIA256-SHA; 28 Feb 2013 16:19:39 +0100 Message-ID: <512F758B.1080400@ulb.ac.be> Date: Thu, 28 Feb 2013 16:19:39 +0100 From: Julien Cigar User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:17.0) Gecko/20130211 Thunderbird/17.0.2 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: SELECT is slow on smaller table? References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------070300030208000903060304" X-Pg-Spam-Score: -4.9 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------070300030208000903060304 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 02/28/2013 16:11, Ao Jianwang wrote: > Hi, > > Does any one can tell me why the same query runs against on smaller > data is slower than bigger table. thanks very much. > > I am using PostgreSQL9.1.8. > > *t_apps_1 and t_estimate_1 are about 300M respectively, while *_list_1 > about 10M more or less. According to the result, it need to read a lot > of blocks(112) from disk.* > explain (ANALYZE ON, BUFFERS ON, verbose on > ) SELECT e.t_id, SUM(e.estimate) as est > FROM > t_estimate_list_1 l, > t_apps_list_1 rl, > t_apps_1 r, > t_estimate_1 e > WHERE > l.id = rl.dsf_id and > l.date = '2012-07-01' and > l.fed_id = 202 and > l.st_id = 143464 and > rl.cat_id = 12201 and > l.id = e.list_id and > rl.id = r.list_id and > r.t_id = e.t_id > GROUP BY e.t_id; > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=2529.91..2530.06 rows=15 width=8) (actual > time=1041.391..1041.409 rows=97 loops=1) > Buffers: shared hit=304 read=112 > -> Nested Loop (cost=0.00..2529.84 rows=15 width=8) (actual > time=96.752..1041.145 rows=97 loops=1) > *Buffers: shared hit=304 read=112* > -> Nested Loop (cost=0.00..312.60 rows=242 width=12) > (actual time=62.035..70.239 rows=97 loops=1) > Buffers: shared hit=18 read=10 > -> Nested Loop (cost=0.00..16.56 rows=1 width=12) > (actual time=19.520..19.521 rows=1 loops=1) > Buffers: shared hit=3 read=6 > -> Index Scan using t_estimate_list_1_unique on t_estimate_list_1 l > (cost=0.00..8.27 rows=1 width=4) (actual time=11.175..11.176 rows=1 > loops=1) > Index Cond: ((date = '2012-07-01'::date) AND (st_id = 143464) AND > (fed_id = 202)) > Buffers: shared hit=2 read=4 > -> Index Scan using t_apps_list_1_unique on t_apps_list_1 rl > (cost=0.00..8.28 rows=1 width=8) (actual time=8.339..8.339 rows=1 > loops=1) > Index Cond: ((dsf_id = l.id ) AND (cat_id = 12201)) > Buffers: shared hit=1 read=2 > -> Index Scan using t_apps_1_pkey on t_apps_1 r > (cost=0.00..288.56 rows=598 width=8) (actual time=42.513..50.676 > rows=97 loops=1) > Index Cond: (list_id = rl.id ) > Buffers: shared hit=15 read=4 > -> Index Scan using t_estimate_1_pkey on t_estimate_1 e > (cost=0.00..9.15 rows=1 width=12) (actual time=10.006..10.007 rows=1 > loops=97) > Index Cond: ((list_id = l.id ) AND (t_id = > r.t_id)) > Buffers: shared hit=286 read=102 > * Total runtime: 1041.511 ms* > (21 rows) > > *The table *_30 are about 30 times larger than *_1 in the above SQL. > According to the result, it need to read a lot of blocks(22) from disk. * > explain (ANALYZE ON, BUFFERS ON > ) SELECT e.t_id, SUM(e.estimate) as est > FROM > t_estimate_list_30 l, > t_apps_list_30 rl, > t_apps_30 r, > t_estimate_30 e > WHERE > l.id = rl.dsf_id and > l.date = '2012-07-01' and > l.fed_id = 202 and > l.st_id = 143464 and > rl.cat_id = 12201 and > l.id = e.list_id and > rl.id = r.list_id and > r.t_id = e.t_id > GROUP BY e.t_id; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=3494.89..3495.04 rows=15 width=8) (actual > time=160.612..160.632 rows=97 loops=1) > Buffers: shared hit=493 read=22 > -> Nested Loop (cost=0.00..3494.81 rows=15 width=8) (actual > time=151.183..160.533 rows=97 loops=1) > *Buffers: shared hit=493 read=22* > -> Nested Loop (cost=0.00..431.42 rows=240 width=12) > (actual time=105.810..106.597 rows=97 loops=1) > Buffers: shared hit=20 read=10 > -> Nested Loop (cost=0.00..16.58 rows=1 width=12) > (actual time=52.804..52.805 rows=1 loops=1) > Buffers: shared hit=4 read=6 > -> Index Scan using t_estimate_list_5_unique on t_estimate_list_5 l > (cost=0.00..8.27 rows=1 width=4) (actual time=19.846..19.846 rows=1 > loops=1) > Index Cond: ((date = '2012-07-01'::date) AND (st_id = 143464) AND > (fed_id = 202)) > Buffers: shared hit=2 read=4 > -> Index Scan using t_apps_list_5_unique on t_apps_list_5 rl > (cost=0.00..8.30 rows=1 width=8) (actual time=32.951..32.952 rows=1 > loops=1) > Index Cond: ((dsf_id = l.id ) AND (cat_id = 12201)) > Buffers: shared hit=2 read=2 > -> Index Scan using t_apps_5_pkey on t_apps_5 r > (cost=0.00..393.68 rows=1693 width=8) (actual time=53.004..53.755 > rows=97 loops=1) > Index Cond: (list_id = rl.id ) > Buffers: shared hit=16 read=4 > -> Index Scan using t_estimate_5_pkey on t_estimate_5 e > (cost=0.00..12.75 rows=1 width=12) (actual time=0.555..0.555 rows=1 > loops=97) > Index Cond: ((list_id = l.id ) AND (t_id = > r.t_id)) > Buffers: shared hit=473 read=12 > * Total runtime: 160.729 ms* > (21 rows) > > Probably that somes pages have to be loaded in memory ... It should be faster if you re-run the same query just after -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. --------------070300030208000903060304 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 02/28/2013 16:11, Ao Jianwang wrote:
Hi,

Does any one can tell me why the same query runs against on smaller data is slower than bigger table. thanks very much.

I am using PostgreSQL9.1.8.

t_apps_1 and t_estimate_1 are about 300M respectively, while *_list_1 about 10M more or less. According to the result, it need to read a lot of blocks(112) from disk.
explain (ANALYZE ON, BUFFERS ON, verbose on
) SELECT e.t_id, SUM(e.estimate) as est
            FROM  
                t_estimate_list_1 l, 
                t_apps_list_1 rl, 
                t_apps_1 r,
                t_estimate_1 e
            WHERE 
                l.id = rl.dsf_id and
                l.date = '2012-07-01' and
                l.fed_id = 202 and
                l.st_id = 143464 and
                rl.cat_id = 12201 and
                l.id = e.list_id and
                rl.id = r.list_id and
                r.t_id = e.t_id
            GROUP BY e.t_id;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2529.91..2530.06 rows=15 width=8) (actual time=1041.391..1041.409 rows=97 loops=1)
   Buffers: shared hit=304 read=112
   ->  Nested Loop  (cost=0.00..2529.84 rows=15 width=8) (actual time=96.752..1041.145 rows=97 loops=1)
         Buffers: shared hit=304 read=112
         ->  Nested Loop  (cost=0.00..312.60 rows=242 width=12) (actual time=62.035..70.239 rows=97 loops=1)
               Buffers: shared hit=18 read=10
               ->  Nested Loop  (cost=0.00..16.56 rows=1 width=12) (actual time=19.520..19.521 rows=1 loops=1)
                     Buffers: shared hit=3 read=6
                     ->  Index Scan using t_estimate_list_1_unique on t_estimate_list_1 l  (cost=0.00..8.27 rows=1 width=4) (actual time=11.175..11.176 rows=1 loops=1)
                           Index Cond: ((date = '2012-07-01'::date) AND (st_id = 143464) AND (fed_id = 202))
                           Buffers: shared hit=2 read=4
                     ->  Index Scan using t_apps_list_1_unique on t_apps_list_1 rl  (cost=0.00..8.28 rows=1 width=8) (actual time=8.339..8.339 rows=1 loops=1)
                           Index Cond: ((dsf_id = l.id) AND (cat_id = 12201))
                           Buffers: shared hit=1 read=2
               ->  Index Scan using t_apps_1_pkey on t_apps_1 r  (cost=0.00..288.56 rows=598 width=8) (actual time=42.513..50.676 rows=97 loops=1)
                     Index Cond: (list_id = rl.id)
                     Buffers: shared hit=15 read=4
         ->  Index Scan using t_estimate_1_pkey on t_estimate_1 e  (cost=0.00..9.15 rows=1 width=12) (actual time=10.006..10.007 rows=1 loops=97)
               Index Cond: ((list_id = l.id) AND (t_id = r.t_id))
               Buffers: shared hit=286 read=102
 Total runtime: 1041.511 ms
(21 rows)

The table *_30 are about 30 times larger than *_1 in the above SQL. According to the result, it need to read a lot of blocks(22) from disk. 
explain (ANALYZE ON, BUFFERS ON
) SELECT e.t_id, SUM(e.estimate) as est
            FROM  
                t_estimate_list_30 l, 
                t_apps_list_30 rl, 
                t_apps_30 r,
                t_estimate_30 e
            WHERE 
                l.id = rl.dsf_id and
                l.date = '2012-07-01' and
                l.fed_id = 202 and
                l.st_id = 143464 and
                rl.cat_id = 12201 and
                l.id = e.list_id and
                rl.id = r.list_id and
                r.t_id = e.t_id
            GROUP BY e.t_id;
                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=3494.89..3495.04 rows=15 width=8) (actual time=160.612..160.632 rows=97 loops=1)
   Buffers: shared hit=493 read=22
   ->  Nested Loop  (cost=0.00..3494.81 rows=15 width=8) (actual time=151.183..160.533 rows=97 loops=1)
         Buffers: shared hit=493 read=22
         ->  Nested Loop  (cost=0.00..431.42 rows=240 width=12) (actual time=105.810..106.597 rows=97 loops=1)
               Buffers: shared hit=20 read=10
               ->  Nested Loop  (cost=0.00..16.58 rows=1 width=12) (actual time=52.804..52.805 rows=1 loops=1)
                     Buffers: shared hit=4 read=6
                     ->  Index Scan using t_estimate_list_5_unique on t_estimate_list_5 l  (cost=0.00..8.27 rows=1 width=4) (actual time=19.846..19.846 rows=1 loops=1)
                           Index Cond: ((date = '2012-07-01'::date) AND (st_id = 143464) AND (fed_id = 202))
                           Buffers: shared hit=2 read=4
                     ->  Index Scan using t_apps_list_5_unique on t_apps_list_5 rl  (cost=0.00..8.30 rows=1 width=8) (actual time=32.951..32.952 rows=1 loops=1)
                           Index Cond: ((dsf_id = l.id) AND (cat_id = 12201))
                           Buffers: shared hit=2 read=2
               ->  Index Scan using t_apps_5_pkey on t_apps_5 r  (cost=0.00..393.68 rows=1693 width=8) (actual time=53.004..53.755 rows=97 loops=1)
                     Index Cond: (list_id = rl.id)
                     Buffers: shared hit=16 read=4
         ->  Index Scan using t_estimate_5_pkey on t_estimate_5 e  (cost=0.00..12.75 rows=1 width=12) (actual time=0.555..0.555 rows=1 loops=97)
               Index Cond: ((list_id = l.id) AND (t_id = r.t_id))
               Buffers: shared hit=473 read=12
 Total runtime: 160.729 ms
(21 rows)



Probably that somes pages have to be loaded in memory ...
It should be faster if you re-run the same query just after

-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--------------070300030208000903060304--