Re: How to improve my slow query for table have list of child table?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alex Lai
Sent: Thursday, January 19, 2012 1:56 PM
To: postgres general support
Subject: [GENERAL] How to improve my slow query for table have list of child
table?

Dear All,

I have a large table that have 8 child tables.
The size of the table is 30 millioins with necessary index needed.

Table filemeta
Column | Type | Modifiers
--------+-------------------+-----------
fileid | integer | not null
esdt | character varying |
key | character varying |
source | character varying |
Indexes:
"pk_filemeta" PRIMARY KEY, btree (fileid)
"ak_filemeta_esdt" btree (esdt)
"ak_filemeta_fileid" btree (fileid)
"ak_filemeta_source" btree (source)
Foreign-key constraints:
"fk_filemeta_esdt" FOREIGN KEY (esdt) REFERENCES esdt_def(esdt)
DEFERRABLE
"fk_filemeta_file" FOREIGN KEY (fileid) REFERENCES file(fileid) ON
DELETE CASCADE
"fk_filemeta_source" FOREIGN KEY (source) REFERENCES source_def(source)
Child tables: filemeta_anc,
filemeta_app,
filemeta_l0,
filemeta_l0r,
filemeta_mdkey,
filemeta_ompslookup,
filemeta_orbital,
filemeta_timerange

Explain analyse select * from filemeta where esdt = 'MET' and key =
'2011-10-08 07:09:47-04';

QUERY
PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------
Result (cost=53295.97..558304.84 rows=42 width=37) (actual
time=1063.016..3770.361 rows=5 loops=1)
-> Append (cost=53295.97..558304.84 rows=42 width=37) (actual
time=1063.013..3770.348 rows=5 loops=1)
-> Bitmap Heap Scan on filemeta (cost=53295.97..370366.99
rows=34 width=35) (actual time=1063.011..2020.002 rows=5 loops=1)
Recheck Cond: ((esdt)::text = 'MET'::text)
Filter: ((key)::text = '2011-10-08 07:09:47-04'::text)
-> Bitmap Index Scan on ak_filemeta_esdt
(cost=0.00..53295.96 rows=3245468 width=0) (actual time=910.287..910.287
rows=3216226 loops=1)
Index Cond: ((esdt)::text = 'MET'::text)
-> Seq Scan on filemeta_anc filemeta (cost=0.00..574.01
rows=1 width=59) (actual time=5.740..5.740 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_app filemeta (cost=0.00..16.30 rows=1
width=100) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_l0 filemeta (cost=0.00..7483.35
rows=1 width=39) (actual time=52.905..52.905 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_l0r filemeta (cost=0.00..123.87
rows=1 width=40) (actual time=1.353..1.353 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_mdkey filemeta (cost=0.00..29707.58
rows=1 width=28) (actual time=273.616..273.616 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Index Scan using pk_filemeta_ompslookup on filemeta_ompslookup
filemeta (cost=0.00..29.84 rows=1 width=45) (actual
time=0.084..0.084 rows=0 loops=1)
Index Cond: ((esdt)::text = 'MET'::text)
Filter: ((key)::text = '2011-10-08 07:09:47-04'::text)
-> Bitmap Heap Scan on filemeta_orbital filemeta
(cost=95.13..5674.40 rows=1 width=22) (actual time=0.021..0.021 rows=0
loops=1)
Recheck Cond: ((esdt)::text = 'MET'::text)
Filter: ((key)::text = '2011-10-08 07:09:47-04'::text)
-> Bitmap Index Scan on id_filemeta_orbital
(cost=0.00..95.13 rows=4173 width=0) (actual time=0.018..0.018 rows=0
loops=1)
Index Cond: ((esdt)::text = 'MET'::text)
-> Seq Scan on filemeta_timerange filemeta
(cost=0.00..144328.49 rows=1 width=44) (actual time=1416.605..1416.605
rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
Total runtime: 3770.529 ms
(28 rows)

I did
set enable_seqscan = off;
but it does not improve much.

Explain analyse select * from filemeta where esdt = 'MET' and key =
'2011-10-08 07:09:47-04';

QUERY
PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------
Result (cost=53295.97..60000558304.84 rows=42 width=37) (actual
time=1003.565..3706.919 rows=5 loops=1)
-> Append (cost=53295.97..60000558304.84 rows=42 width=37) (actual
time=1003.562..3706.907 rows=5 loops=1)
-> Bitmap Heap Scan on filemeta (cost=53295.97..370366.99
rows=34 width=35) (actual time=1003.560..1960.436 rows=5 loops=1)
Recheck Cond: ((esdt)::text = 'MET'::text)
Filter: ((key)::text = '2011-10-08 07:09:47-04'::text)
-> Bitmap Index Scan on ak_filemeta_esdt
(cost=0.00..53295.96 rows=3245468 width=0) (actual time=853.507..853.507
rows=3216226 loops=1)
Index Cond: ((esdt)::text = 'MET'::text)
-> Seq Scan on filemeta_anc filemeta
(cost=10000000000.00..10000000574.01 rows=1 width=59) (actual
time=7.124..7.124 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_app filemeta
(cost=10000000000.00..10000000016.30 rows=1 width=100) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_l0 filemeta
(cost=10000000000.00..10000007483.35 rows=1 width=39) (actual
time=52.270..52.270 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_l0r filemeta
(cost=10000000000.00..10000000123.87 rows=1 width=40) (actual
time=1.339..1.339 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Seq Scan on filemeta_mdkey filemeta
(cost=10000000000.00..10000029707.58 rows=1 width=28) (actual
time=267.798..267.798 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
-> Index Scan using pk_filemeta_ompslookup on filemeta_ompslookup
filemeta (cost=0.00..29.84 rows=1 width=45) (actual
time=0.080..0.080 rows=0 loops=1)
Index Cond: ((esdt)::text = 'MET'::text)
Filter: ((key)::text = '2011-10-08 07:09:47-04'::text)
-> Bitmap Heap Scan on filemeta_orbital filemeta
(cost=95.13..5674.40 rows=1 width=22) (actual time=0.023..0.023 rows=0
loops=1)
Recheck Cond: ((esdt)::text = 'MET'::text)
Filter: ((key)::text = '2011-10-08 07:09:47-04'::text)
-> Bitmap Index Scan on id_filemeta_orbital
(cost=0.00..95.13 rows=4173 width=0) (actual time=0.020..0.020 rows=0
loops=1)
Index Cond: ((esdt)::text = 'MET'::text)
-> Seq Scan on filemeta_timerange filemeta
(cost=10000000000.00..10000144328.49 rows=1 width=44) (actual
time=1417.817..1417.817 rows=0 loops=1)
Filter: (((esdt)::text = 'MET'::text) AND ((key)::text =
'2011-10-08 07:09:47-04'::text))
Total runtime: 3707.097 ms
(28 rows)

It looks to me postgres still Seq Scan it's child tables.
Normally, a simple query on the 30 millions rows with proper indexing will
only take about 1 second.
Any idea are welcome.

--
Best regards,

Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
alai(at)sesda2(dot)com

-------------------------------------------------------------------

You need to create indexes on the children before you can expect an index to
be used.

http://www.postgresql.org/docs/9.0/interactive/ddl-inherit.html [Section
5.8.1]

"A serious limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single tables,
not to their inheritance children. This is true on both the referencing and
referenced sides of a foreign key constraint."

David J.