X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 0EB4D310E90 for ; Wed, 2 Jul 2003 10:28:23 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 40017-01 for ; Wed, 2 Jul 2003 07:28:12 -0300 (ADT) Received: from mwinf0101.wanadoo.fr (smtp8.wanadoo.fr [193.252.22.30]) by svr1.postgresql.org (Postfix) with ESMTP id CA42231013F for ; Wed, 2 Jul 2003 07:28:11 -0300 (ADT) Received: from Paris (ABoulogne-110-1-2-97.w80-13.abo.wanadoo.fr [80.13.94.97]) by mwinf0101.wanadoo.fr (SMTP Server) with ESMTP id C575EC000132; Wed, 2 Jul 2003 12:28:10 +0200 (CEST) Reply-To: From: "Michael Mattox" To: "Postgresql Performance" Cc: "Rod Taylor" Subject: Re: How to optimize monstrous query, sorts instead of Date: Wed, 2 Jul 2003 12:28:10 +0200 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_000C_01C34095.65E66A90" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Archive-Number: 200307/24 X-Sequence-Number: 2317 This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C34095.65E66A90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit > My postgresql.conf is attached in case I have it configured incorrectly. Forgot my postgres.conf.. ------=_NextPart_000_000C_01C34095.65E66A90 Content-Type: application/octet-stream; name="postgresql.conf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="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. # # 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". #=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # # Connection Parameters # tcpip_socket =3D true #ssl =3D false max_connections =3D 200 #superuser_reserved_connections =3D 2 port =3D 5432=20 #hostname_lookup =3D false #show_source_port =3D false #unix_socket_directory =3D '' #unix_socket_group =3D '' #unix_socket_permissions =3D 0777 # octal #virtual_host =3D '' #krb_server_keyfile =3D '' # # Shared Memory Size # shared_buffers =3D 32768 # min max_connections*2 or 16, 8KB each max_fsm_relations =3D 4000 # min 10, fsm is free space map, ~40 bytes max_fsm_pages =3D 20000 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction =3D 64 # min 10 wal_buffers =3D 20 # min 4, typically 8KB each # # Non-shared Memory Sizes # sort_mem =3D 25600 # min 64, size in KB vacuum_mem =3D 51200 # min 1024, size in KB # # Write-ahead log (WAL) # #checkpoint_segments =3D 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout =3D 300 # range 30-3600, in seconds # #commit_delay =3D 0 # range 0-100000, in microseconds #commit_siblings =3D 5 # range 1-1000 # #fsync =3D true #wal_sync_method =3D fsync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug =3D 0 # range 0-16 # # Optimizer Parameters # #enable_seqscan =3D true #enable_indexscan =3D true #enable_tidscan =3D true #enable_sort =3D true #enable_nestloop =3D true #enable_mergejoin =3D true #enable_hashjoin =3D true effective_cache_size =3D 32768 # typically 8KB each random_page_cost =3D 1.5 # units are one sequential page fetch cost #cpu_tuple_cost =3D 0.01 # (same) #cpu_index_tuple_cost =3D 0.001 # (same) #cpu_operator_cost =3D 0.0025 # (same) #default_statistics_target =3D 10 # range 1-1000 # # GEQO Optimizer Parameters # #geqo =3D true #geqo_selection_bias =3D 2.0 # range 1.5-2.0 #geqo_threshold =3D 11 #geqo_pool_size =3D 0 # default based on tables in statement,=20 # range 128-1024 #geqo_effort =3D 1 #geqo_generations =3D 0 #geqo_random_seed =3D -1 # auto-compute seed # # Message display # server_min_messages =3D log # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic client_min_messages =3D log # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error silent_mode =3D false log_connections =3D true #log_pid =3D false log_statement =3D true log_duration =3D true log_timestamp =3D true log_min_error_statement =3D info # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #debug_print_parse =3D false #debug_print_rewritten =3D false #debug_print_plan =3D false #debug_pretty_print =3D false #explain_pretty_print =3D true # requires USE_ASSERT_CHECKING #debug_assertions =3D true # # Syslog # #syslog =3D 0 # range 0-2 #syslog_facility =3D 'LOCAL0' #syslog_ident =3D 'postgres' # # Statistics # #show_parser_stats =3D false #show_planner_stats =3D false #show_executor_stats =3D false #show_statement_stats =3D false # requires BTREE_BUILD_STATS #show_btree_build_stats =3D false # # Access statistics collection # #stats_start_collector =3D true #stats_reset_on_server_start =3D true #stats_command_string =3D false #stats_row_level =3D false #stats_block_level =3D false # # Lock Tracing # #trace_notify =3D false # requires LOCK_DEBUG #trace_locks =3D false #trace_userlocks =3D false #trace_lwlocks =3D false #debug_deadlocks =3D false #trace_lock_oidmin =3D 16384 #trace_lock_table =3D 0 # # Misc # #autocommit =3D true #dynamic_library_path =3D '$libdir' #search_path =3D '$user,public' #datestyle =3D 'iso, us' #timezone =3D unknown # actually, defaults to TZ environment setting #australian_timezones =3D false #client_encoding =3D sql_ascii # actually, defaults to database encoding #authentication_timeout =3D 60 # 1-600, in seconds #deadlock_timeout =3D 1000 # in milliseconds #default_transaction_isolation =3D 'read committed' #max_expr_depth =3D 10000 # min 10 #max_files_per_process =3D 1000 # min 25 #password_encryption =3D true #sql_inheritance =3D true #transform_null_equals =3D false #statement_timeout =3D 0 # 0 is disabled, in milliseconds #db_user_namespace =3D false =20 # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES =3D 'en_GB.UTF-8' LC_MONETARY =3D 'en_GB.UTF-8' LC_NUMERIC =3D 'en_GB.UTF-8' LC_TIME =3D 'en_GB.UTF-8' ------=_NextPart_000_000C_01C34095.65E66A90--