To: pgsql-performance@postgresql.org
From: Vladimir Yavoskiy <vovka@krevedko.su>
Subject: query partitioned table is very slow
Message-ID: <5621740E.3010002@krevedko.su>
Date: Sat, 17 Oct 2015 01:02:54 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I have about 900 partitioned tables with 67 millons rows.
And I found that my query takes too much time!
------------------------------------------------------------------------------
explain  ( ANALYZE,VERBOSE,BUFFERS )
  select report_id from cars."all"
     WHERE
     report_datetime  = '2015-10-14 00:02:02+03'::timestamptz   AND
     report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid
------------------------------------------------------------------------------
Append  (cost=0.00..4.43 rows=2 width=4) (actual time=0.023..0.023 
rows=1 loops=1)
   Buffers: shared hit=4
   ->  Seq Scan on cars.all  (cost=0.00..0.00 rows=1 width=4) (actual 
time=0.002..0.002 rows=0 loops=1)
         Output: all.report_id
         Filter: ((all.report_datetime = '2015-10-14 
00:02:02+03'::timestamp with time zone) AND (all.report_uuid = 
'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid))
   ->  Index Scan using day_151014_uuid_idx on cars.day_151014 
(cost=0.42..4.43 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=1)
         Output: day_151014.report_id
         Index Cond: (day_151014.report_uuid = 
'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid)
         Filter: (day_151014.report_datetime = '2015-10-14 
00:02:02+03'::timestamp with time zone)
         Buffers: shared hit=4
Total runtime: 0.096 ms
------------------------------------------------------------------------------

This query takes about 500ms. But query from only part-table takes 12ms:

select report_id from cars.day_151014
     WHERE
     report_datetime  = '2015-10-14 00:02:02+03'::timestamptz   AND
     report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid
------------------------------------------------------------------------------
  explain  ( ANALYZE,VERBOSE,BUFFERS )
  select report_id from cars.day_151014
     WHERE
     report_datetime  = '2015-10-14 00:02:02+03'::timestamptz   AND
     report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid
------------------------------------------------------------------------------
Index Scan using day_151014_uuid_idx on cars.day_151014 (cost=0.42..4.43 
rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=1)
   Output: report_id
   Index Cond: (day_151014.report_uuid = 
'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid)
   Filter: (day_151014.report_datetime = '2015-10-14 
00:02:02+03'::timestamp with time zone)
   Buffers: shared hit=4
Total runtime: 0.045 ms
------------------------------------------------------------------------------

Query plans seems fine, but why actual query is so slow?

p.s. PostgreSQL 9.3.9 x86_64 on Oracle Linux Server release 6.6 
(3.8.13-68.3.2.el6uek.x86_64)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance