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)
a...@sesda2.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
