Re: Query only slow on first run

Поиск
Список
Период
Сортировка
От Jean-David Beyer
Тема Re: Query only slow on first run
Дата
Msg-id 474DE5B1.1010101@verizon.net
обсуждение исходный текст
Ответ на Re: Query only slow on first run  (cluster <skrald@amossen.dk>)
Список pgsql-performance
cluster wrote:
>> The indexes don't contain visibility information, so Postgres has to
>> look up the row on disk to verify it isn't dead.
>
> I guess this fact drastically decreases the performance. :-( The number
> of rows with a random_number will just grow over time while the number of
> questions with status = 1 will always be somewhat constant at about
> 10.000 or most likely much less.
>
> I could really use any kind of suggestion on how to improve the query in
> order to make it scale better for large data sets The 6-7000 ms for a
> clean run is really a showstopper. Need to get it below 70 ms somehow.
>
Here is a suggestion that I have not tried. This might not make sense,
depending on how often you do this.

Make two tables whose DDL is almost the same. In one, put all the rows with
status = 1, and in the other put all the rows whose status != 1.

Now all the other queries you run would probably need to join both tables,
so maybe you make a hash index on the right fields so that would go fast.

Now for the status = 1 queries, you just look at that smaller table. This
would obviously be faster.

For the other queries, you would get stuck with the join. You would have to
weigh the overall performance issue vs. the performance of this special query.


--
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 16:55:01 up 2 days, 22:43, 0 users, load average: 4.31, 4.32, 4.20

В списке pgsql-performance по дате отправления:

Предыдущее
От: cluster
Дата:
Сообщение: Re: Query only slow on first run
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query only slow on first run