On Thu, May 28, 2009 at 5:52 PM, Simon Riggs
<simon@2ndquadrant.com> wrote:
On Thu, 2009-05-28 at 15:03 -0400, Douglas Alan wrote:
> 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.
You should look at indexing solutions, or take professional advice on
that.
I'm not sure that I know what an "indexing solution" is.
> Users can specify fairly arbitrary search criteria. All of the
> queries should perform well. By "well", I mean within 10 seconds or
> so.
That's an unrealistic expectation, unfortunately. Fast search requires
some form of preparatory action and without any clue as to what that
should be you cannot guarantee response times.
Unrealistic or not, it mostly currently works, modulo Postgres sometimes deciding to do a slow sequence scan when there is a perfectly good index.
For instance, I just tried a query that results in 137,042 results (out of 150 million rows) is constrained in several different ways, involves a table join on another large table, and it took nine seconds.
|>ouglas