pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery partitioned table is very slow
2+ messages / 2 participants
[nested] [flat]
* query partitioned table is very slow
@ 2015-10-16 22:02 Vladimir Yavoskiy <vovka@krevedko.su>
0 siblings, 1 reply; 2+ messages in thread
From: Vladimir Yavoskiy @ 2015-10-16 22:02 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: query partitioned table is very slow
@ 2015-10-16 23:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vladimir Yavoskiy <vovka@krevedko.su>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2015-10-16 23:02 UTC (permalink / raw)
To: Vladimir Yavoskiy <vovka@krevedko.su>; +Cc: pgsql-performance
Vladimir Yavoskiy <vovka@krevedko.su> writes:
> I have about 900 partitioned tables with 67 millons rows.
> And I found that my query takes too much time!
That's about 100X partitions too many for that amount of rows.
Partitions are a good thing in small doses, otherwise planning
time will kill you.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-10-16 23:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-10-16 22:02 query partitioned table is very slow Vladimir Yavoskiy <vovka@krevedko.su>
2015-10-16 23:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox