Re: RES: RES: [PERFORM] select on 1milion register = 6s
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 [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpQf4XPs38J4.pgp Description: PGP signature
RES: RES: [PERFORM] select on 1milion register = 6s
Look it EXPLAIN ANALYZE select to_char(data_encerramento,'mm/') as opcoes_mes, to_char(data_encerramento,'-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, '-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/') as opcoes_mes, to_char(data_encerramento,'-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, '-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:[EMAIL PROTECTED] 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 +, Ragnar wrote: On lau, 2007-07-28 at 17:12 -0300, Bruno Rodrigues Siqueira wrote: where to_char( data_encerramento ,'-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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: RES: RES: [PERFORM] select on 1milion register = 6s
On Sun, Jul 29, 2007 at 01:44:23PM -0300, Bruno Rodrigues Siqueira wrote: EXPLAIN ANALYZE select to_char(data_encerramento,'mm/') as opcoes_mes, to_char(data_encerramento,'-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, '-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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp7DXcKrZWrf.pgp Description: PGP signature
RES: RES: [PERFORM] select on 1milion register = 6s
Data_encerramento is a timestamp column I will try your tip. Thanks -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ,'-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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
