From: | Douglas Alan <darkwater42(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Subject: | Re: What is the right way to deal with a table with rows that are not in a random order? |
Date: | 2009-05-28 22:02:30 |
Message-ID: | ce6334d00905281502k4a33c607n1479486d08e53902@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, May 28, 2009 at 5:52 PM, Simon Riggs <simon(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig de Stigter | 2009-05-28 22:12:22 | pg_stats.avg_width differs by a factor of 4 on different machines |
Previous Message | Simon Riggs | 2009-05-28 21:52:50 | Re: What is the right way to deal with a table with rows that are not in a random order? |