-----Original Message----- From: Alex Lai [mailto:alai@xxxxxxxxxx] Sent: Thursday, January 19, 2012 3:12 PM To: David Johnston Cc: 'postgres general support' Subject: Re: How to improve my slow query for table have list of child table? David Johnston wrote: > -----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, > > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
