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

David Johnston wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.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@sesda2.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.
>
>
>
>
>
Hi David,

I created a table copy all the 30 millions rows from filemeta table. The
new created table has
no inherit child tables associated. I ran the same query and got the
time down to 2.8 seconds from 4.5 seconds.
The cost impacted by inherit around 1.8 seconds.

I also noticed the column 'key' has no index on that I use to search.
Although column 'key' has 25% rows are null.
I was able to created index on column 'key'. I ran the same query and
got the time down to 1.9 seconds form 2.8 seconds.

I wonder there are any work around to over come the inherit issue by not
restructure the schema.

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