From: Pedro Jiménez Pérez <p.jimenez@ismsolar.com>
To: pgsql-performance@postgresql.org
Subject: Re: limit order by performance issue
Date: Wed, 17 Oct 2012 11:14:05 +0200
Message-ID: <507E76DD.9020802@ismsolar.com> (raw)
In-Reply-To: <CAFj8pRBKGMCyGR8uAhz0RkrXqa+06TOp1j52aCPVzeYAs+hmng@mail.gmail.com>
References: <507C4B8E.3010806@ismsolar.com>
<CAFj8pRBKGMCyGR8uAhz0RkrXqa+06TOp1j52aCPVzeYAs+hmng@mail.gmail.com>
For this query:
select var_value from ism_floatvalues where id_signal = 29660 order by
time_stamp desc limit 1;
This is what EXPLAIN returns (can't make EXPLAIN ANALYZE because it
"never" ends):
"Limit (cost=0.00..258.58 rows=1 width=16)"
" -> Index Scan Backward using ism_floatvalues_index_time_stamp on
ism_floatvalues (cost=0.00..8912076.82 rows=34466 width=16)"
" Filter: (id_signal = 29660)"
This is EXPLAIN ANALYZE without "limit 1":
"Sort (cost=93683.39..93769.56 rows=34466 width=16) (actual
time=188.643..188.650 rows=1 loops=1)"
" Sort Key: time_stamp"
" Sort Method: quicksort Memory: 17kB"
" -> Index Scan using ism_floatvalues_index on ism_floatvalues
(cost=0.00..90494.38 rows=34466 width=16) (actual time=188.019..188.030
rows=1 loops=1)"
" Index Cond: (id_signal = 29660)"
"Total runtime: 189.033 ms"
Note that I have created two indexes, the first on id_signal and the
second on time_stamp.
Regards.
El 16/10/2012 21:23, Pavel Stehule escribió:
> 2012/10/15 Pedro Jiménez <p.jimenez@ismsolar.com>:
>> Hello,
>> I'm trying to do a simple SQL query over Postgresl 9.0 running on Ubuntu.
>>
>> I have a large table (over 100 million records) with three fields, id_signal
>> (bigint), time_stamp (timestamp) and var_value (float).
>>
>> My query looks like this:
>>
>> select var_value from ism_floatvalues where id_signal = 29660 order by
>> time_stamp desc limit 1;
>>
>> So I want to select the last value from a determinated ID (is_signal).
>>
>> This query runs FOREVER, while if I delete "limit 1" it runs instantly....
> did you ANALYZE your tables?
>
> Can you send EXPLAIN ANALYZE result of both queries?
>
> Regards
>
> Pavel Stehule
>
>
>> Any help?
>>
>> Regards.
>>
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
--
Documento sin título
**Pedro Jiménez Pérez
**p.jimenez@ismsolar.com
****
**Innovación en Sistemas de Monitorización, S.L.**
Edificio Hevimar
C/ Iván Pavlov 2 y 4 - Parcela 4 2ª Planta Local 9
Parque Tecnológico de Andalucía
29590 Campanillas (Málaga)
Tlfno. 952 02 07 13
contacto@ismsolar.com
firma_gpt.jpg, 1 kB
Antes de imprimir, piensa en tu responsabilidad y compromiso con el
MEDIO AMBIENTE!
Before printing, think about your responsibility and commitment with the
ENVIRONMENT!
CLÁUSULA DE CONFIDENCIALIDAD.- Este mensaje, y en su caso, cualquier
fichero anexo al mismo, puede contener información confidencial o
legalmente protegida (LOPD 15/1999 de 13 de Diciembre), siendo para uso
exclusivo del destinatario. No hay renuncia a la confidencialidad o
secreto profesional por cualquier transmisión defectuosa o errónea, y
queda expresamente prohibida su divulgación, copia o distribución a
terceros sin la autorización expresa del remitente. Si ha recibido este
mensaje por error, se ruega lo notifique al remitente enviando un
mensaje al correo electrónico contacto@ismsolar.com y proceda
inmediatamente al borrado del mensaje original y de todas sus copias.
Gracias por su colaboración.
Attachments:Logo_Ism.gif (image/gif, 1.4K) downloadview imagegraphics2 (image/jpeg, 4.3K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: p.jimenez@ismsolar.com
Subject: Re: limit order by performance issue
In-Reply-To: <507E76DD.9020802@ismsolar.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox