Re: tuning

Alejandro:

A primera vista se me ocurre lo siguiente :

1. No hay primary key definida ni ningun otro indice.
2. La consulta es suficientemente compleja como para que tarde bastante
si no hay indices adecuados.

Sugiero definir algunos indices:

CREATE INDEX radacct_by_cliente ON radacct USING btree (cliente)
CREATE INDEX radacct_by_acctstarttime ON radacct USING btree
(acctstarttime);

y luego probar de nuevo la consulta.

Esto es muy elemental, habria que estudiarlo un poco mas con EXPLAIN
como sugirio Alvaro.
Por otro lado: no parece muy apropiado utilizar un campo varchar(50)
como identificador de cliente, salvo que sea estrictamente necesario.

Diego.

On mar, 2004-06-08 at 18:11, Alejandro Casanova wrote:

> 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.
> >
> >

Diego A. Gil
Director
Admin S.A.