Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx


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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux