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
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) pgp5eYJsdKDrX.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: [PERFORM] select on 1milion register = 6s
Ok. Query EXPLAIN ANALYZE select to_char(data_encerramento,'mm/') as opcoes_mes, to_char(data_encerramento,'-mm') as ordem from detalhamento_bas where to_char( data_encerramento ,'-mm') between '2006-12' and '2007-01' GROUP BY opcoes_mes, ordem ORDER BY ordem DESC QUERY RESULT QUERY PLAN Sort (cost=11449.37..11449.40 rows=119 width=8) (actual time=14431.537..14431.538 rows=2 loops=1) Sort Key: to_char(data_encerramento, '-mm'::text) - HashAggregate (cost=11448.79..11448.96 rows=119 width=8) (actual time=14431.521..14431.523 rows=2 loops=1) - Index Scan using detalhamento_bas_idx3003 on detalhamento_bas (cost=0.00..11442.95 rows=11679 width=8) (actual time=0.135..12719.155 rows=2335819 loops=1) Index Cond: ((to_char(data_encerramento, '-mm'::text) = '2006-12'::text) AND (to_char(data_encerramento, '-mm'::text) = '2007-01'::text)) Total runtime: 14431.605 ms SERVER DELL PowerEdge 2950 XEON Quad-Core 3.0Ghz 4Gb RAM Linux CentOS 5.0 64-bits Postgres 8.1.4 Postgresql.conf # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 10 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 5 # min 16 or max_connections*2, 8KB each temp_buffers = 1000 # min 100,
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
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
RES: [PERFORM] select on 1milion register = 6s
Yes. Look this... and please, tell me if you can help me... Thanks Query EXPLAIN ANALYZE select to_char(data_encerramento,'mm/') as opcoes_mes, to_char(data_encerramento,'-mm') as ordem from detalhamento_bas where to_char( data_encerramento ,'-mm') between '2006-12' and '2007-01' GROUP BY opcoes_mes, ordem ORDER BY ordem DESC QUERY RESULT QUERY PLAN Sort (cost=11449.37..11449.40 rows=119 width=8) (actual time=14431.537..14431.538 rows=2 loops=1) Sort Key: to_char(data_encerramento, '-mm'::text) - HashAggregate (cost=11448.79..11448.96 rows=119 width=8) (actual time=14431.521..14431.523 rows=2 loops=1) - Index Scan using detalhamento_bas_idx3003 on detalhamento_bas (cost=0.00..11442.95 rows=11679 width=8) (actual time=0.135..12719.155 rows=2335819 loops=1) Index Cond: ((to_char(data_encerramento, '-mm'::text) = '2006-12'::text) AND (to_char(data_encerramento, '-mm'::text) = '2007-01'::text)) Total runtime: 14431.605 ms SERVER DELL PowerEdge 2950 XEON Quad-Core 3.0Ghz 4Gb RAM Linux CentOS 5.0 64-bits Postgres 8.1.4 Postgresql.conf # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 10 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 5 # min 16 or max_connections*2, 8KB each
RES: [PERFORM] select on 1milion register = 6s
Yes, i do. -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de Hervé Piedvache Enviada em: sábado, 28 de julho de 2007 16:57 Para: pgsql-performance@postgresql.org Cc: Bruno Rodrigues Siqueira Assunto: Re: [PERFORM] select on 1milion register = 6s Do you have analyzed your table before doing this ? Le samedi 28 juillet 2007, Bruno Rodrigues Siqueira a écrit : Friends, Who can help me? My SELECT in a base with 1 milion register, using expression index = 6seconds. Please, I don't know how to makes it better. Thanks -- Hervé Piedvache ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
