Re: Query with limit takes hours

Поиск
Список
Период
Сортировка
От Vincent
Тема Re: Query with limit takes hours
Дата
Msg-id 507A7375.7000903@xs4all.nl
обсуждение исходный текст
Ответ на Query with limit takes hours  (henk de wit <henk53602@hotmail.com>)
Список pgsql-admin
On 10/14/2012 08:56 AM, henk de wit wrote:
On PG 9.1 and 9.2 I'm running the following query:

SELECT 
    *
FROM 
    stream_store 
JOIN
    (
        SELECT 
            UNNEST(stream_store_ids) AS id
        FROM 
            stream_store_version_index 
        WHERE 
            stream_id = 607106 AND
            version = 11
    ) AS records USING (id)
ORDER BY 
    id DESC

This takes several (10 to 20) milliseconds at most.

When I add a LIMIT 1 to the end of the query, the query time goes to several hours(!).

The full version String of PG 9.1 is "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit". The 9.1 machine is a socket 771 dual quad core at 3.16Ghz with 64GB memory and 10 Intel x25M SSDs in a RAID5 setup on 2 ARECA 1680 RAID controllers. The "stream_store" table has 122 million rows and is partitioned. The array that's being unnested for the join has 27 entries.

Any idea?

I'd look at the difference in the output of EXPLAIN for both queries, that's bound to tell you something...

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

Предыдущее
От: henk de wit
Дата:
Сообщение: Query with limit takes hours
Следующее
От: Jimy McDermott
Дата:
Сообщение: TCPIP connection on local host using md5