We have a table with 4 million records and we created partitions for that table, assuming the select queries will be faster on partition-enabled tables. However, the select on partition-enabled tables are 2x slow!!
On normal table (24 ms)
explain analyse select * from tbl_original where device_info_id = 5;
On partition-enabled table (49 ms)
explain analyse select * from tbl_partitioned where device_info_id = 5;
Following is the output of the EXPLAIN ANALYZE command for tbl_original:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_original (cost=61.19..9515.02 rows=2679 width=379) (actual time=0.297..13.008 rows=3369 loops=1)
Recheck Cond: (device_info_id = 5)
Heap Blocks: exact=554
-> Bitmap Index Scan on idx_tbl_original (cost=0.00..60.52 rows=2679 width=0) (actual time=0.232..0.232 rows=3369 loops=1)
Index Cond: (device_info_id = 5)
Planning time: 0.087 ms
Execution time: 24.890 ms
Following is the output of the EXPLAIN ANALYZE command for tbl_partitioned
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..6251.14 rows=3697 width=404) (actual time=0.034..36.635 rows=3369 loops=1)
-> Seq Scan on tbl_partitioned (cost=0.00..0.00 rows=1 width=1069) (actual time=0.006..0.006 rows=0 loops=1)
Filter: (device_info_id = 5)
-> Index Scan using idx_tbl_partitioned_p1 on tbl_partitioned_p1 (cost=0.42..6251.14 rows=3696 width=404) (actual time=0.017..12.922 rows=3369 loops=1)
Index Cond: (device_info_id = 5)
Planning time: 0.184 ms
Execution time: 49.129 ms
Looks like the most expensive operation in the partitioned query is Index Scan taking 6251.14 units. However, considering the size of the partition table as compared to the original table this Index Scan should have been very fast. Not sure if we are missing anything obvious here!
Any help in optimizing the query/partitioned table will be appreciated.
Note: The partitioned table was created using the following:
CREATE TABLE tbl_partitioned (LIKE tbl_original);
CREATE TABLE tbl_partitioned_p1 (
CONSTRAINT pk_tbl_partitioned_p1 PRIMARY KEY (id),
CONSTRAINT ck_tbl_partitioned_p1 CHECK ( device_info_id < 10 )
) INHERITS (tbl_partitioned);
CREATE INDEX idx_tbl_partitioned_p1 ON tbl_partitioned_p1 (device_info_id);
CREATE INDEX idx_tbl_partitioned ON tbl_partitioned (device_info_id);
INSERT INTO tbl_partitioned_p1 SELECT * from tbl_original where device_info_id < 10;
The sizes of the tables are :
select count(*) from tbl_partitioned; -- 413696 rows
select count(*) from tbl_original; -- 4417025 rows
select count(*) from tbl_original where device_info_id = 5; -- 3369 rows
constraint_exclusion is set to partition
Try getting more EXPLAIN DATA like:
explain (ANALYZE, TIMING, COSTS, BUFFERS, VERBOSE) select * from tbl_original where device_info_id = 5;
In particular, pay attention to the "hits" in the ouptut like:
Buffers: shared hit=4 read=224
Read=xxx means that a block had to be read from disk. Hit= means that it came from RAM (shared buffers). It's possible that more of your data is in shared buffers -- performance is very dependent on that.