Re: What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 4:14 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> 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