From: "Bruno Rodrigues Siqueira" <bruno@ravnus.com>
To: <pgsql-performance@postgresql.org>
Subject: RES: select on 1milion register = 6s
Date: Sat, 28 Jul 2007 17:12:10 -0300
Message-ID: <002001c7d153$944f3c60$e900a8c0@brunolaptop>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcfRUMGonMRAn80BTkaXO+5EZHUdSAAAEIsA
In-Reply-To: <46ABA01E.8000802@emolecules.com>


Ok.
=09

	Query

EXPLAIN=20
ANALYZE
select=20
       to_char(data_encerramento,'mm/yyyy')  as opcoes_mes,=20
       to_char(data_encerramento,'yyyy-mm') as ordem
from detalhamento_bas
where

to_char( data_encerramento ,'yyyy-mm')=20
between   '2006-12' and  '2007-01'

GROUP BY opcoes_mes, ordem
ORDER BY ordem DESC


	QUERY RESULT

QUERY PLAN
Sort  (cost=3D11449.37..11449.40 rows=3D119 width=3D8) (actual
time=3D14431.537..14431.538 rows=3D2 loops=3D1)
  Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
  ->  HashAggregate  (cost=3D11448.79..11448.96 rows=3D119 width=3D8) =
(actual
time=3D14431.521..14431.523 rows=3D2 loops=3D1)
        ->  Index Scan using detalhamento_bas_idx3003 on =
detalhamento_bas
(cost=3D0.00..11442.95 rows=3D11679 width=3D8) (actual =
time=3D0.135..12719.155
rows=3D2335819 loops=3D1)
              Index Cond: ((to_char(data_encerramento, 'yyyy-mm'::text) =
>=3D
'2006-12'::text) AND (to_char(data_encerramento, 'yyyy-mm'::text) <=3D
'2007-01'::text))
Total runtime: 14431.605 ms=09


	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 =3D value
#
# (The '=3D' 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=3Don'. 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=20
# to SIGHUP the postmaster for the changes to take effect, or use=20
# "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 =3D 'ConfigDir'		# use data in another directory
#hba_file =3D 'ConfigDir/pg_hba.conf'	# host-based authentication file
#ident_file =3D 'ConfigDir/pg_ident.conf'	# IDENT configuration file

# If external_pid_file is not explicitly set, no extra pid file is =
written.
#external_pid_file =3D '(none)'		# write an extra pid file


#------------------------------------------------------------------------=
---
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------=
---

# - Connection Settings -

listen_addresses =3D '*'		# what IP address(es) to listen on;=20
					# comma-separated list of addresses;
					# defaults to 'localhost', '*' =3D all
#port =3D 5432
max_connections =3D 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 =3D 2
#unix_socket_directory =3D ''
#unix_socket_group =3D ''
#unix_socket_permissions =3D 0777		# octal
#bonjour_name =3D ''			# defaults to the computer name

# - Security & Authentication -

#authentication_timeout =3D 60		# 1-600, in seconds
#ssl =3D off
#password_encryption =3D on
#db_user_namespace =3D off

# Kerberos
#krb_server_keyfile =3D ''
#krb_srvname =3D 'postgres'
#krb_server_hostname =3D ''		# empty string matches any keytab
entry
#krb_caseins_users =3D off

# - TCP Keepalives -
# see 'man 7 tcp' for details

#tcp_keepalives_idle =3D 0		# TCP_KEEPIDLE, in seconds;
					# 0 selects the system default
#tcp_keepalives_interval =3D 0		# TCP_KEEPINTVL, in seconds;
					# 0 selects the system default
#tcp_keepalives_count =3D 0		# TCP_KEEPCNT;
					# 0 selects the system default


#------------------------------------------------------------------------=
---
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------=
---

# - Memory -

shared_buffers =3D 50000			# min 16 or max_connections*2, 8KB
each
temp_buffers =3D 1000			# min 100, 8KB each
#max_prepared_transactions =3D 5		# can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem =3D 3145728			# min 64, size in KB
maintenance_work_mem =3D 4194304		# min 1024, size in KB
max_stack_depth =3D 2048			# min 100, size in KB

# - Free Space Map -

max_fsm_pages =3D 208000			# min max_fsm_relations*16, 6 bytes
each
max_fsm_relations =3D 10000		# min 100, ~70 bytes each

# - Kernel Resource Usage -

#max_files_per_process =3D 1000		# min 25
#preload_libraries =3D ''

# - Cost-Based Vacuum Delay -

vacuum_cost_delay =3D 50			# 0-1000 milliseconds
#vacuum_cost_page_hit =3D 1		# 0-10000 credits
#vacuum_cost_page_miss =3D 10		# 0-10000 credits
#vacuum_cost_page_dirty =3D 20		# 0-10000 credits
#vacuum_cost_limit =3D 200		# 0-10000 credits

# - Background writer -

bgwriter_delay =3D 200			# 10-10000 milliseconds between
rounds
bgwriter_lru_percent =3D 20.0		# 0-100% of LRU buffers
scanned/round
bgwriter_lru_maxpages =3D 100		# 0-1000 buffers max written/round
bgwriter_all_percent =3D 3		# 0-100% of all buffers
scanned/round
bgwriter_all_maxpages =3D 600		# 0-1000 buffers max written/round


#------------------------------------------------------------------------=
---
# WRITE AHEAD LOG
#------------------------------------------------------------------------=
---

# - Settings -

fsync =3D off				# turns forced synchronization on or
off
#wal_sync_method =3D fsync		# the default is the first option=20
					# supported by the operating system:
					#   open_datasync
					#   fdatasync
					#   fsync
					#   fsync_writethrough
					#   open_sync
full_page_writes =3D off			# recover from partial page writes
wal_buffers =3D 2300			# min 4, 8KB each
commit_delay =3D 10			# range 0-100000, in microseconds
#commit_siblings =3D 5			# range 1-1000

# - Checkpoints -

checkpoint_segments =3D 256		# in logfile segments, min 1, 16MB
each
checkpoint_timeout =3D 300		# range 30-3600, in seconds
checkpoint_warning =3D 99		# in seconds, 0 is off

# - Archiving -

#archive_command =3D ''			# command to use to archive a
logfile=20
					# segment


#------------------------------------------------------------------------=
---
# QUERY TUNING
#------------------------------------------------------------------------=
---

# - Planner Method Configuration -

enable_bitmapscan =3D on
enable_hashagg =3D on
enable_hashjoin =3D on
enable_indexscan =3D on
enable_mergejoin =3D on
enable_nestloop =3D on
enable_seqscan =3D on
enable_sort =3D on
enable_tidscan =3D on

# - Planner Cost Constants -

effective_cache_size =3D 41943040		# typically 8KB each
random_page_cost =3D 1			# units are one sequential page
fetch=20
					# cost
cpu_tuple_cost =3D 0.001			# (same)
cpu_index_tuple_cost =3D 0.0005		# (same)
cpu_operator_cost =3D 0.00025		# (same)

# - Genetic Query Optimizer -

#geqo =3D on
#geqo_threshold =3D 12
#geqo_effort =3D 5			# range 1-10
#geqo_pool_size =3D 0			# selects default based on effort
#geqo_generations =3D 0			# selects default based on effort
#geqo_selection_bias =3D 2.0		# range 1.5-2.0

# - Other Planner Options -

#default_statistics_target =3D 10		# range 1-1000
constraint_exclusion =3D on
#from_collapse_limit =3D 8
join_collapse_limit =3D 1		# 1 disables collapsing of explicit=20
					# JOINs


#------------------------------------------------------------------------=
---
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------=
---

# - Where to Log -

#log_destination =3D 'stderr'		# Valid values are combinations of=20
					# stderr, syslog and eventlog,=20
					# depending on platform.

# This is used when logging to stderr:
redirect_stderr =3D on			# Enable capturing of stderr into
log=20
					# files

# These are only used if redirect_stderr is on:
log_directory =3D 'pg_log'		# Directory where log files are
written
					# Can be absolute or relative to
PGDATA
log_filename =3D 'postgresql-%a.log'	# Log file name pattern.
					# Can include strftime() escapes
log_truncate_on_rotation =3D on	# If on, any existing log file of the =
same=20
					# name as the new log file will be
					# truncated rather than appended to.
But
					# such truncation only occurs on
					# time-driven rotation, not on
restarts
					# or size-driven rotation. Default
is
					# off, meaning append to existing
files
					# in all cases.
log_rotation_age =3D 1440			# Automatic rotation of logfiles
will=20
					# happen after so many minutes.  0
to=20
					# disable.
log_rotation_size =3D 0			# Automatic rotation of logfiles
will=20
					# happen after so many kilobytes of
log
					# output.  0 to disable.

# These are relevant when logging to syslog:
#syslog_facility =3D 'LOCAL0'
#syslog_ident =3D 'postgres'


# - When to Log -

#client_min_messages =3D notice		# Values, in order of decreasing
detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   log
					#   notice
					#   warning
					#   error

#log_min_messages =3D notice		# Values, in order of decreasing
detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic

#log_error_verbosity =3D default		# terse, default, or verbose
messages

#log_min_error_statement =3D panic	# Values in order of increasing
severity:
				 	#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
				 	#   info
					#   notice
					#   warning
					#   error
					#   panic(off)
				=20
#log_min_duration_statement =3D -1	# -1 is disabled, 0 logs all
statements
					# and their durations, in
milliseconds.

#silent_mode =3D off			# DO NOT USE without syslog or=20
					# redirect_stderr

# - What to Log -

#debug_print_parse =3D off
#debug_print_rewritten =3D off
#debug_print_plan =3D off
#debug_pretty_print =3D off
#log_connections =3D off
#log_disconnections =3D off
#log_duration =3D off
#log_line_prefix =3D ''			# Special values:
					#   %u =3D user name
					#   %d =3D database name
					#   %r =3D remote host and port
					#   %h =3D remote host
					#   %p =3D PID
					#   %t =3D timestamp (no milliseconds)
					#   %m =3D timestamp with milliseconds
					#   %i =3D command tag
					#   %c =3D session id
					#   %l =3D session line number
					#   %s =3D session start timestamp
					#   %x =3D transaction id
					#   %q =3D stop here in non-session=20
					#        processes
					#   %% =3D '%'
					# e.g. '<%u%%%d> '
#log_statement =3D 'none'			# none, mod, ddl, all
#log_hostname =3D off


#------------------------------------------------------------------------=
---
# RUNTIME STATISTICS
#------------------------------------------------------------------------=
---

# - Statistics Monitoring -

#log_parser_stats =3D off
#log_planner_stats =3D off
#log_executor_stats =3D off
#log_statement_stats =3D off

# - Query/Index Statistics Collector -

stats_start_collector =3D off
#stats_command_string =3D off
#stats_block_level =3D off
#stats_row_level =3D off
#stats_reset_on_server_start =3D off


#------------------------------------------------------------------------=
---
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------=
---

autovacuum =3D on			# enable autovacuum subprocess?
#autovacuum_naptime =3D 60		# time between autovacuum runs, in
secs
#autovacuum_vacuum_threshold =3D 1000	# min # of tuple updates before
					# vacuum
#autovacuum_analyze_threshold =3D 500	# min # of tuple updates before=20
					# analyze
#autovacuum_vacuum_scale_factor =3D 0.4	# fraction of rel size before=20
					# vacuum
#autovacuum_analyze_scale_factor =3D 0.2	# fraction of rel size before=20
					# analyze
#autovacuum_vacuum_cost_delay =3D -1	# default vacuum cost delay for=20
					# autovac, -1 means use=20
					# vacuum_cost_delay
#autovacuum_vacuum_cost_limit =3D -1	# default vacuum cost limit for=20
					# autovac, -1 means use
					# vacuum_cost_limit


#------------------------------------------------------------------------=
---
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------=
---

# - Statement Behavior -

#search_path =3D '$user,public'		# schema names
#default_tablespace =3D ''		# a tablespace name, '' uses
					# the default
#check_function_bodies =3D on
default_transaction_isolation =3D 'read committed'
#default_transaction_read_only =3D off
#statement_timeout =3D 0			# 0 is disabled, in milliseconds

# - Locale and Formatting -

datestyle =3D 'iso, dmy'
#timezone =3D unknown			# actually, defaults to TZ=20
					# environment setting
#australian_timezones =3D off
#extra_float_digits =3D 0			# min -15, max 2
client_encoding =3D LATIN1		# actually, defaults to database
					# encoding

# These settings are initialized by initdb -- they might be changed
lc_messages =3D 'pt_BR.ISO-8859-1'			# locale for system
error message=20
					# strings
lc_monetary =3D 'pt_BR.ISO-8859-1'			# locale for
monetary formatting
lc_numeric =3D 'pt_BR.ISO-8859-1'			# locale for number
formatting
lc_time =3D 'pt_BR.ISO-8859-1'				# locale for time
formatting

# - Other Defaults -

#explain_pretty_print =3D on
#dynamic_library_path =3D '$libdir'


#------------------------------------------------------------------------=
---
# LOCK MANAGEMENT
#------------------------------------------------------------------------=
---

deadlock_timeout =3D 1000		# in milliseconds
#max_locks_per_transaction =3D 64		# min 10
# note: each lock table slot uses ~220 bytes of shared memory, and there =
are
# max_locks_per_transaction * (max_connections + =
max_prepared_transactions)
# lock table slots.


#------------------------------------------------------------------------=
---
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------=
---

# - Previous Postgres Versions -

#add_missing_from =3D off
#backslash_quote =3D safe_encoding	# on, off, or safe_encoding
#default_with_oids =3D off
#escape_string_warning =3D off
#regex_flavor =3D advanced		# advanced, extended, or basic
#sql_inheritance =3D on

# - Other Platforms & Clients -

#transform_null_equals =3D off


#------------------------------------------------------------------------=
---
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------=
---

#custom_variable_classes =3D ''		# list of custom variable class
names


-----Mensagem original-----
De: Craig James [mailto:craig_james@emolecules.com]=20
Enviada em: s=E1bado, 28 de julho de 2007 16:59
Para: Bruno Rodrigues Siqueira; pgsql-performance@postgresql.org
Assunto: Re: [PERFORM] select on 1milion register =3D 6s

Bruno Rodrigues Siqueira wrote:
>             Who can help me? My SELECT in a base with 1 milion =
register,=20
> using  expression index =3D 6seconds=85

Run your query using=20

   EXPLAIN ANALYZE SELECT ... your query ...

and then post the results to this newsgroup.  Nobody can help until they =
see
the results of EXPLAIN ANALYZE.  Also, include all other relevant
information, such as Postgres version, operating system, amount of =
memory,
and any changes you have made to the Postgres configuration file.

Craig