On Thu, May 28, 2009 at 4:14 AM, Simon Riggs <simon@xxxxxxxxxxxxxxx> wrote: > Partition the table, then scan the correct partition. If I do that, will Postgres figure out the "right thing" to do if the parent table is queried instead? Also, what are the performance implications then for doing queries that span all the partitions, which will be the norm for our application? The application in question is a kind of data warehousing thing (of astronomical stars), and there's an ORM in the middle, so it's not easy for us to hand-tune how individual queries are specified. Instead, we have to structure the database and the indexes so that things generally perform well, without having to tweak specific queries. Users can specify fairly arbitrary search criteria. All of the queries should perform well. By "well", I mean within 10 seconds or so. Scanning all of the 150 million rows takes much longer than 10 seconds, unfortunately. Any one of these "solutions" will cause Postgres to do an index scan in the problematic case where Postgres is deciding to a sequential scan. The index scan performs snappily enough: - Using "order by" on the query. - Changing the search value for the column to a value that occurs less frequently. - Fetching the value to search for via a sub-query so that Postgres can't determine a priori that the value being searched value occurs so commonly. Unfortunately, as I mentioned, due to the ORM, none of these solutions really work for us in practice, as opposed to at a psql prompt. |>ouglas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
