Skip site navigation (1) Skip section navigation (2)

Re: sloooow query

From: "Marie G(dot) Tuite" <marie(dot)tuite(at)edisonaffiliates(dot)com>
To: "Justin Clift" <justin(at)postgresql(dot)org>, <josh(at)agliodbs(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: sloooow query
Date: 2002-10-07 19:53:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-performance
Here is a show all:


project-# ;
NOTICE:  enable_seqscan is on
NOTICE:  enable_indexscan is on
NOTICE:  enable_tidscan is on
NOTICE:  enable_sort is on
NOTICE:  enable_nestloop is on
NOTICE:  enable_mergejoin is on
NOTICE:  enable_hashjoin is on
NOTICE:  ksqo is off
NOTICE:  geqo is on
NOTICE:  tcpip_socket is on
NOTICE:  ssl is off
NOTICE:  fsync is on
NOTICE:  silent_mode is off
NOTICE:  log_connections is off
NOTICE:  log_timestamp is off
NOTICE:  log_pid is off
NOTICE:  debug_print_query is off
NOTICE:  debug_print_parse is off
NOTICE:  debug_print_rewritten is off
NOTICE:  debug_print_plan is off
NOTICE:  debug_pretty_print is off
NOTICE:  show_parser_stats is off
NOTICE:  show_planner_stats is off
NOTICE:  show_executor_stats is off
NOTICE:  show_query_stats is off
NOTICE:  stats_start_collector is on
NOTICE:  stats_reset_on_server_start is on
NOTICE:  stats_command_string is off
NOTICE:  stats_row_level is off
NOTICE:  stats_block_level is off
NOTICE:  trace_notify is off
NOTICE:  hostname_lookup is off
NOTICE:  show_source_port is off
NOTICE:  sql_inheritance is on
NOTICE:  australian_timezones is off
NOTICE:  fixbtree is on
NOTICE:  password_encryption is off
NOTICE:  transform_null_equals is off
NOTICE:  geqo_threshold is 11
NOTICE:  geqo_pool_size is 0
NOTICE:  geqo_effort is 1
NOTICE:  geqo_generations is 0
NOTICE:  geqo_random_seed is -1
NOTICE:  deadlock_timeout is 1000
NOTICE:  syslog is 0
NOTICE:  max_connections is 64
NOTICE:  shared_buffers is 128
NOTICE:  port is 5432
NOTICE:  unix_socket_permissions is 511
NOTICE:  sort_mem is 1024
NOTICE:  vacuum_mem is 8192
NOTICE:  max_files_per_process is 1000
NOTICE:  debug_level is 0
NOTICE:  max_expr_depth is 10000
NOTICE:  max_fsm_relations is 100
NOTICE:  max_fsm_pages is 10000
NOTICE:  max_locks_per_transaction is 64
NOTICE:  authentication_timeout is 60
NOTICE:  pre_auth_delay is 0
NOTICE:  checkpoint_segments is 3
NOTICE:  checkpoint_timeout is 300
NOTICE:  wal_buffers is 8
NOTICE:  wal_files is 0
NOTICE:  wal_debug is 0
NOTICE:  commit_delay is 0
NOTICE:  commit_siblings is 5
NOTICE:  effective_cache_size is 1000
NOTICE:  random_page_cost is 4
NOTICE:  cpu_tuple_cost is 0.01
NOTICE:  cpu_index_tuple_cost is 0.001
NOTICE:  cpu_operator_cost is 0.0025
NOTICE:  geqo_selection_bias is 2
NOTICE:  default_transaction_isolation is read committed
NOTICE:  dynamic_library_path is $libdir
NOTICE:  krb_server_keyfile is FILE:/etc/pgsql/krb5.keytab
NOTICE:  syslog_facility is LOCAL0
NOTICE:  syslog_ident is postgres
NOTICE:  unix_socket_group is unset
NOTICE:  unix_socket_directory is unset
NOTICE:  virtual_host is unset
NOTICE:  wal_sync_method is fdatasync
NOTICE:  DateStyle is ISO with US (NonEuropean) conventions
NOTICE:  Time zone is unset
NOTICE:  Current client encoding is 'SQL_ASCII'
NOTICE:  Current server encoding is 'SQL_ASCII'
NOTICE:  Seed for random number generator is unavailable

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Justin Clift
> Sent: Monday, October 07, 2002 2:30 PM
> To: josh(at)agliodbs(dot)com
> Cc: marie(dot)tuite(at)edisonaffiliates(dot)com; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [pgsql-performance] sloooow query
> Josh Berkus wrote:
> >
> > Marie,
> >
> > > I am experiencing slow db performance.  I have vacuumed,
> analyzed, reindexed
> > > using the force option and performance remains the same -
> dog-slow :(  If I
> > > drop and recreate the database, performance is normal, so
> this suggests a
> > > problem with the indexes?  I also took a look at the
> postgresql.conf and all
> > > appears fine.  There are many instances of the same database
> running on
> > > different servers and not all servers are experiencing the problem.
> >
> > Please post the following:
> > 1) A copy of the relevant portions of your database schema.
> > 2) The query that is running slowly.
> > 3) The results of running EXPLAIN on that query.
> > 4) Your PostgreSQL version and operating system
> > 5) Any other relevant information about your databases, such as
> the quantity
> > of inserts and deletes on the relevant tables.
> 6) And the sort_mem, shared_buffers, vacuum_mem, wal_buffers, and
> wal_files settings from your postgresql.conf file, if possible.
> :-)
> Regards and best wishes,
> Justin Clift
> > --
> > -Josh Berkus
> >  Aglio Database Solutions
> >  San Francisco
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

In response to


pgsql-performance by date

Next:From: Josh BerkusDate: 2002-10-07 20:12:21
Subject: Re: sloooow query
Previous:From: Marie G. TuiteDate: 2002-10-07 19:49:16
Subject: Re: sloooow query

pgsql-general by date

Next:From: Roberto (SmartBit)Date: 2002-10-07 19:57:24
Subject: firstest doubts...
Previous:From: Marie G. TuiteDate: 2002-10-07 19:49:16
Subject: Re: sloooow query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group