-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alex Lai Sent: Thursday, January 19, 2012 1:56 PM To: postgres general support Subject: 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@xxxxxxxxxx ------------------------------------------------------------------- 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
