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

-----Original Message-----
From: Alex Lai [mailto:alai(at)sesda2(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)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.