On 02/15/2011 08:23 AM, Kim A. Brandt wrote:
does `postgres (PostgreSQL) 8.4.5' use the LIMIT of a query when it is run on a partitioned-table or am I doing something wrong? It looks as if postgres queries all partitions and then LIMITing the records afterwards!? This results in a long (>3 minutes) running query. What can I do to optimise this?
Make sure you have constraint_exclusion set to 'on' in your config. Also, what are your checks for your partitions? You've got a pretty wide range in your 'ts' checks, so if you're using them as your partition definition, you're not helping yourself.
The main issue might just be that you've used an order clause. LIMIT 1000 or not, even if it can restrict the result set based on your CHECK criteria, it'll still need to select every matching row from every matched partition, order the results, and chop off the first 1000.
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
