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

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

From

Alex Lai

Date:

19 January 2012, 14:56:13

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

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

From

"David Johnston"

Date:

19 January 2012, 15:10:17

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

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

From

Alex Lai

Date:

19 January 2012, 16:12:36

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

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

From

"David Johnston"

Date:

19 January 2012, 18:56:25

-----Original Message-----
From: Alex Lai [mailto:alai@sesda2.com]
Sent: Thursday, January 19, 2012 3:12 PM
To: David Johnston
Cc: 'postgres general support'
Subject: Re: [GENERAL] How to improve my slow query for table have list of
child table?

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,
>
> 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.
>
> --
>
> 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."
>

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.

----------------------------------------------------------------------------
----------

Alex,

I do not see a scenario where you added indexes for "estd" to the child
tables and then ran the query.

Inheritance works best when you are able to determine that the desired
values will appear on only a single child table so that the data from the
other tables (index or scan) can be completely ignored.  Since you are
dealing with multiple-table results the performance on a single table is
likely to be better than the performance of the partition-group; regardless
of indexes.

Also, you cannot take a single run of a query against two scenarios and
directly compare them and expect meaningful results.  Even if you are doing
things manually you should probably execute each query 5-10 times and then
throw out the first couple of times for each set.  Then provide the simple
average of the remaining attempts and possibly just include all of the
response times for completeness.

David J.