How to improve my slow query for table have list of child table? - Mailing list pgsql-general

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@sesda2.com