Re: tuning

hello gracias ok

la estructara de la tabla es :

CREATE TABLE radacct (
id character varying(50),
nas_ip inet,
remoteaddress inet,
destinyaddress inet,
number character varying(50),
acctstarttime timestamp with time zone,
acctstoptime timestamp with time zone,
duration double precision,
disconnectcause character varying(10),
voicequality integer,
callorigin character varying(10),
accttime timestamp with time zone,
calltype character varying(10),
cliente character varying(50),
proveedor character varying(50),
time_cliente double precision,
time_proveedor double precision,
ruta_cliente character varying(50),
rate_cliente double precision,
valor_cliente double precision,
ruta_proveedor character varying(50),
rate_proveedor double precision,
valor_proveedor double precision
) WITHOUT OIDS;

el query es

SELECT distinct rate_cliente,min(to_char(acctstarttime,'YYYY-MM-DD')) as
fecha_min,max(to_char(acctstarttime,'YYYY-MM-DD')) as
fecha_max,ruta_cliente, sum(time_cliente) as total_minutos,count(*) as
total_llamadas FROM radacct WHERE time_cliente > 0 and cliente = 'XXXXX'
and acctstarttime between '2004-05-01 00:00:00' and '2004-05-07
23:59:59' GROUP BY ruta_cliente, rate_cliente ORDER BY ruta_cliente;

gracias man

Alejandro

On Tue, 2004-06-08 at 14:59, Diego Gil wrote:
> Alejandro:
>
> No soy experto en las opciones de postgresql.conf, pero de todas
> formas estimo que tienes que optimizar la definicion de las tablas,
> indices y consultas antes de revisar postgresql.conf.
>
> Si puedes, muestranos las tablas, indices y consultas y tal vez te
> pueda hacer algunas sugerencias.
>
> Diego.
>
>
> On mar, 2004-06-08 at 17:52, Alejandro Casanova wrote:
> > Este es elarchivo de 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.
> > #
> > # 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".
> >
> >
> > #========================================================================
> >
> >
> > #
> > # Connection Parameters
> > #
> > tcpip_socket = true
> > #ssl = false
> >
> > max_connections = 120
> > #superuser_reserved_connections = 2
> >
> > port = 5432
> > #hostname_lookup = false
> > #show_source_port = false
> >
> > #unix_socket_directory = ''
> > #unix_socket_group = ''
> > #unix_socket_permissions = 0777 # octal
> >
> > #virtual_host = ''
> >
> > #krb_server_keyfile = ''
> >
> >
> > #
> > # Shared Memory Size
> > #
> > shared_buffers = 480 # min max_connections*2 or 16, 8KB each
> > #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
> > #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
> > #max_locks_per_transaction = 64 # min 10
> > #wal_buffers = 8 # min 4, typically 8KB each
> >
> > #
> > # Non-shared Memory Sizes
> > #
> > sort_mem = 4096 # min 64, size in KB
> > #vacuum_mem = 8192 # min 1024, size in KB
> >
> >
> > #
> > # Write-ahead log (WAL)
> > #
> > #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
> > #checkpoint_timeout = 300 # range 30-3600, in seconds
> > #
> > #commit_delay = 0 # range 0-100000, in microseconds
> > #commit_siblings = 5 # range 1-1000
> > #
> > #fsync = true
> > #wal_sync_method = fsync # the default varies across platforms:
> > # # fsync, fdatasync, open_sync, or open_datasync
> > #wal_debug = 0 # range 0-16
> >
> >
> > #
> > # Optimizer Parameters
> > #
> > #enable_seqscan = true
> > #enable_indexscan = true
> > #enable_tidscan = true
> > #enable_sort = true
> > #enable_nestloop = true
> > #enable_mergejoin = true
> > #enable_hashjoin = true
> >
> > #effective_cache_size = 1000 # typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
> > #cpu_tuple_cost = 0.01 # (same)
> > #cpu_index_tuple_cost = 0.001 # (same)
> > #cpu_operator_cost = 0.0025 # (same)
> >
> > #default_statistics_target = 10 # range 1-1000
> >
> > #
> > # GEQO Optimizer Parameters
> > #
> > #geqo = true
> > #geqo_selection_bias = 2.0 # range 1.5-2.0
> > #geqo_threshold = 11
> > #geqo_pool_size = 0 # default based on tables in statement,
> > # range 128-1024
> > #geqo_effort = 1
> > #geqo_generations = 0
> > #geqo_random_seed = -1 # auto-compute seed
> >
> >
> > #
> > # Message display
> > #
> > #server_min_messages = notice # Values, in order of decreasing detail:
> > # debug5, debug4, debug3, debug2, debug1,
> > # info, notice, warning, error, log, fatal,
> > # panic
> > #client_min_messages = notice # Values, in order of decreasing detail:
> > # debug5, debug4, debug3, debug2, debug1,
> > # log, info, notice, warning, error
> > #silent_mode = false
> >
> > #log_connections = false
> > #log_pid = false
> > #log_statement = false
> > #log_duration = false
> > #log_timestamp = false
> >
> > #log_min_error_statement = error # Values in order of increasing
> > severity:
> > # debug5, debug4, debug3, debug2, debug1,
> > # info, notice, warning, error, panic(off)
> >
> > #debug_print_parse = false
> > #debug_print_rewritten = false
> > #debug_print_plan = false
> > #debug_pretty_print = false
> >
> > #explain_pretty_print = true
> >
> > # requires USE_ASSERT_CHECKING
> > #debug_assertions = true
> >
> >
> > #
> > # Syslog
> > #
> > #syslog = 0 # range 0-2
> > #syslog_facility = 'LOCAL0'
> > #syslog_ident = 'postgres'
> >
> >
> > #
> > # Statistics
> > #
> > #show_parser_stats = false
> > #show_planner_stats = false
> > #show_executor_stats = false
> > #show_statement_stats = false
> >
> > # requires BTREE_BUILD_STATS
> > #show_btree_build_stats = false
> >
> >
> > #
> > # Access statistics collection
> > #
> > #stats_start_collector = true
> > #stats_reset_on_server_start = true
> > #stats_command_string = false
> > #stats_row_level = false
> > #stats_block_level = false
> >
> >
> > #
> > # Lock Tracing
> > #
> > #trace_notify = false
> >
> > # requires LOCK_DEBUG
> > #trace_locks = false
> > #trace_userlocks = false
> > #trace_lwlocks = false
> > #debug_deadlocks = false
> > #trace_lock_oidmin = 16384
> > #trace_lock_table = 0
> >
> >
> > #
> > # Misc
> > #
> > #autocommit = true
> > #dynamic_library_path = '$libdir'
> > #search_path = '$user,public'
> > #datestyle = 'iso, us'
> > #timezone = unknown # actually, defaults to TZ environment setting
> > #australian_timezones = false
> > #client_encoding = sql_ascii # actually, defaults to database encoding
> > #authentication_timeout = 60 # 1-600, in seconds
> > #deadlock_timeout = 1000 # in milliseconds
> > #default_transaction_isolation = 'read committed'
> > #max_expr_depth = 10000 # min 10
> > #max_files_per_process = 1000 # min 25
> > #password_encryption = true
> > #sql_inheritance = true
> > #transform_null_equals = false
> > #statement_timeout = 0 # 0 is disabled, in milliseconds
> > #db_user_namespace = false
> >
> >
> >
> > #
> > # Locale settings
> > #
> > # (initialized by initdb -- may be changed)
> > LC_MESSAGES = 'en_US.UTF-8'
> > LC_MONETARY = 'en_US.UTF-8'
> > LC_NUMERIC = 'en_US.UTF-8'
> > LC_TIME = 'en_US.UTF-8'
> >
> >
> >
> >
> >
> >
> > On Tue, 2004-06-08 at 14:31, Diego Gil wrote:
> > > Habria que ver como estan definidas las bases de datos y las
> > > consultas.
> > >
> > > Diego.
> > >
> > > On mar, 2004-06-08 at 17:12, Alejandro Casanova wrote:
> > > > hello
> > > >
> > > > tengo un problema
> > > >
> > > > tengo un equipo con dos procesadores xeon de 2.8 4 gigas en ram y las
> > > > conssultas en postgres son lentas con 9 millones de registros, se demora
> > > > hasta un minito , he tratado de de realizar todos los tinung que
> > > > encuentro pero ninguno hacelera el proceso.
> > > >
> > > > espero que me puedan ayudar
> > > >
> > > >
> > > >
> > > > saludos alejandro
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 7: no olvides aumentar la configuración del "free space map"
> > >
> > >
> > > Diego A. Gil
> > >
> > > Director
> > >
> > > Admin S.A.
> > >
> > >
>
>
> Diego A. Gil
>
> Director
>
> Admin S.A.
>
>