pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRES: RES: select on 1milion register = 6s
4+ messages / 2 participants
[nested] [flat]
* RES: RES: select on 1milion register = 6s
@ 2007-07-28 22:38 Bruno Rodrigues Siqueira <bruno@ravnus.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Rodrigues Siqueira @ 2007-07-28 22:38 UTC (permalink / raw)
To: 'Ragnar' <gnari@hive.is>; +Cc: pgsql-performance
Data_encerramento is a timestamp column
I will try your tip.
Thanks
-----Mensagem original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] Em nome de Ragnar
Enviada em: sábado, 28 de julho de 2007 19:36
Para: Bruno Rodrigues Siqueira
Cc: pgsql-performance@postgresql.org
Assunto: Re: RES: [PERFORM] select on 1milion register = 6s
On lau, 2007-07-28 at 17:12 -0300, Bruno Rodrigues Siqueira wrote:
> where
>
> to_char( data_encerramento ,'yyyy-mm')
> between '2006-12' and '2007-01'
assuming data_encerramento is a date column, try:
WHERE data_encerramento between '2006-12-01' and '2007-01-31'
gnari
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 4+ messages in thread
* RES: RES: select on 1milion register = 6s
@ 2007-07-29 16:44 Bruno Rodrigues Siqueira <bruno@ravnus.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Rodrigues Siqueira @ 2007-07-29 16:44 UTC (permalink / raw)
To: 'Decibel!' <decibel@decibel.org>; 'Ragnar' <gnari@hive.is>; +Cc: pgsql-performance
Look it
EXPLAIN
ANALYZE
select
to_char(data_encerramento,'mm/yyyy') as opcoes_mes,
to_char(data_encerramento,'yyyy-mm') as ordem from detalhamento_bas
where
data_encerramento = '01/12/2006'
GROUP BY opcoes_mes, ordem
ORDER BY ordem DESC
****************************************************************************
QUERY PLAN
Sort (cost=60.72..60.72 rows=1 width=8) (actual time=4.586..4.586 rows=0
loops=1)
Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
-> HashAggregate (cost=60.72..60.72 rows=1 width=8) (actual
time=4.579..4.579 rows=0 loops=1)
-> Index Scan using detalhamento_bas_idx3005 on detalhamento_bas
(cost=0.00..60.67 rows=105 width=8) (actual time=4.576..4.576 rows=0
loops=1)
Index Cond: (data_encerramento = '2006-12-01
00:00:00'::timestamp without time zone)
Total runtime: 4.629 ms
////////////////////////////////////////////////////////////////////////////
EXPLAIN
ANALYZE
select
to_char(data_encerramento,'mm/yyyy') as opcoes_mes,
to_char(data_encerramento,'yyyy-mm') as ordem from detalhamento_bas
where
data_encerramento >= '01/12/2006' and
data_encerramento < '01/02/2007'
GROUP BY opcoes_mes, ordem
ORDER BY ordem DESC
****************************************************************************
QUERY PLAN
Sort (cost=219113.10..219113.10 rows=4 width=8) (actual
time=10079.212..10079.213 rows=2 loops=1)
Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
-> HashAggregate (cost=219113.09..219113.09 rows=4 width=8) (actual
time=10079.193..10079.195 rows=2 loops=1)
-> Seq Scan on detalhamento_bas (cost=0.00..217945.41 rows=2335358
width=8) (actual time=0.041..8535.792 rows=2335819 loops=1)
Filter: ((data_encerramento >= '2006-12-01
00:00:00'::timestamp without time zone) AND (data_encerramento < '2007-02-01
00:00:00'::timestamp without time zone))
Total runtime: 10079.256 ms
Strange!!! Why does the index not works?
All my querys doesn't work with range dates.... I don't know what to do...
Please, help!
Bruno
-----Mensagem original-----
De: Decibel! [mailto:decibel@decibel.org]
Enviada em: domingo, 29 de julho de 2007 13:36
Para: Ragnar
Cc: Bruno Rodrigues Siqueira; pgsql-performance@postgresql.org
Assunto: Re: RES: [PERFORM] select on 1milion register = 6s
On Sat, Jul 28, 2007 at 10:36:16PM +0000, Ragnar wrote:
> On lau, 2007-07-28 at 17:12 -0300, Bruno Rodrigues Siqueira wrote:
>
> > where
> >
> > to_char( data_encerramento ,'yyyy-mm')
> > between '2006-12' and '2007-01'
>
> assuming data_encerramento is a date column, try:
> WHERE data_encerramento between '2006-12-01' and '2007-01-31'
IMO, much better would be:
WHERE data_encerramento >= '2006-12-01' AND data_encerramento <
'2007-02-01'
This means you don't have to worry about last day of the month or
timestamp precision. In fact, since the field is actually a timestamp,
the between posted above won't work correctly.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: RES: select on 1milion register = 6s
@ 2007-07-30 01:29 Decibel! <decibel@decibel.org>
parent: Bruno Rodrigues Siqueira <bruno@ravnus.com>
0 siblings, 1 reply; 4+ messages in thread
From: Decibel! @ 2007-07-30 01:29 UTC (permalink / raw)
To: Bruno Rodrigues Siqueira <bruno@ravnus.com>; +Cc: Ragnar <gnari@hive.is>; pgsql-performance
On Sun, Jul 29, 2007 at 01:44:23PM -0300, Bruno Rodrigues Siqueira wrote:
> EXPLAIN
> ANALYZE
> select
> to_char(data_encerramento,'mm/yyyy') as opcoes_mes,
> to_char(data_encerramento,'yyyy-mm') as ordem from detalhamento_bas
> where
>
> data_encerramento >= '01/12/2006' and
> data_encerramento < '01/02/2007'
>
> GROUP BY opcoes_mes, ordem
> ORDER BY ordem DESC
>
> ****************************************************************************
>
> QUERY PLAN
> Sort (cost=219113.10..219113.10 rows=4 width=8) (actual
> time=10079.212..10079.213 rows=2 loops=1)
> Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
> -> HashAggregate (cost=219113.09..219113.09 rows=4 width=8) (actual
> time=10079.193..10079.195 rows=2 loops=1)
> -> Seq Scan on detalhamento_bas (cost=0.00..217945.41 rows=2335358
> width=8) (actual time=0.041..8535.792 rows=2335819 loops=1)
> Filter: ((data_encerramento >= '2006-12-01
> 00:00:00'::timestamp without time zone) AND (data_encerramento < '2007-02-01
> 00:00:00'::timestamp without time zone))
> Total runtime: 10079.256 ms
>
> Strange!!! Why does the index not works?
It's unlikely that it's going to be faster to index scan 2.3M rows than
to sequential scan them. Try setting enable_seqscan=false and see if it
is or not.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: RES: select on 1milion register = 6s
@ 2007-07-30 06:52 Decibel! <decibel@decibel.org>
parent: Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 4+ messages in thread
From: Decibel! @ 2007-07-30 06:52 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-perform@postgresql.org
Please reply-all so others can learn and contribute.
On Sun, Jul 29, 2007 at 09:38:12PM -0700, Craig James wrote:
> Decibel! wrote:
> >It's unlikely that it's going to be faster to index scan 2.3M rows than
> >to sequential scan them. Try setting enable_seqscan=false and see if it
> >is or not.
>
> Out of curiosity ... Doesn't that depend on the table? Are all of the data
> for one row stored contiguously, or are the data stored column-wise? If
> it's the former, and the table has hundreds of columns, or a few columns
> with large text strings, then wouldn't the time for a sequential scan
> depend not on the number of rows, but rather the total amount of data?
Yes, the time for a seqscan is mostly dependent on table size and not
the number of rows. But the number of rows plays a very large role in
the cost of an indexscan.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-07-30 06:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-28 22:38 RES: RES: select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-29 16:44 RES: RES: select on 1milion register = 6s Bruno Rodrigues Siqueira <bruno@ravnus.com>
2007-07-30 01:29 ` Decibel! <decibel@decibel.org>
2007-07-30 06:52 ` Decibel! <decibel@decibel.org>
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