Re: vacuum analyze slows sql query

Поиск
Список
Период
Сортировка
От Pierre-Frédéric Caillaud
Тема Re: vacuum analyze slows sql query
Дата
Msg-id opsg57tnztcq72hf@musicbox
обсуждение исходный текст
Ответ на Re: vacuum analyze slows sql query  (patrick ~ <sidsrr@yahoo.com>)
Список pgsql-performance
>> Lets say for a second that you manage to trick it into using index scan,
>> and then you actually call the function with one of the values that
>> returns 1,000s of rows. Probably it will take 10-100 times longer than
>> if it used a seq scan.

> I don't know if it matters (I suspect that it does) but I am using
> LIMIT 1 in the sub-query/stored function.  All I need is one single
> row meeting any of the criteria laid out in the stored procedure to
> establish an offer_id is "pending".

    So, in your case if you LIMIT the index scan will always be fast, and the
seq scan will be catastrophic, because you don't need to retrieve all the
rows, but just one. (IMHO the planner screws these LIMIT clauses becauses
it expects the data to be randomly distributed in the first page while in
real life it's not).

    You could use EXIST to test the existence of a subquery (after all, thats
its purpose), or you could :

    When SELECT ... FROM table WHERE stuff=value LIMIT 1
    obstinately uses a seq scan, spray a little order by :

    When SELECT ... FROM table WHERE stuff=value ORDER BY stuff LIMIT 1

    the ORDER BY will make the planner think "I could use the index to
order"...

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

Предыдущее
От: John Meinel
Дата:
Сообщение: Re: vacuum analyze slows sql query
Следующее
От: Erik Norvelle
Дата:
Сообщение: Slow performance with Group By