[postgis-users] Query crashed

Stephen Woodbridge woodbri at swoodbridge.com
Tue Nov 22 06:48:35 PST 2005


Hi Mark,

 From top:

last pid: 70096;  load averages:  0.00,  0.00,  0.00   up 117+01:48:34 
09:31:36
40 processes:  1 running, 39 sleeping
CPU states:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100% 
idle
Mem: 53M Active, 716M Inact, 170M Wired, 53M Cache, 111M Buf, 3376K Free
Swap: 4096M Total, 948K Used, 4095M Free


canada=# SHOW ALL;
               name              |                setting
--------------------------------+---------------------------------------
  add_missing_from               | on
  archive_command                | unset
  australian_timezones           | off
  authentication_timeout         | 60
  bgwriter_delay                 | 200
  bgwriter_maxpages              | 100
  bgwriter_percent               | 1
  block_size                     | 8192
  check_function_bodies          | on
  checkpoint_segments            | 3
  checkpoint_timeout             | 300
  checkpoint_warning             | 30
  client_encoding                | LATIN1
  client_min_messages            | notice
  commit_delay                   | 0
  commit_siblings                | 5
  config_file                    | /usr/local/pgsql/data/postgresql.conf
  cpu_index_tuple_cost           | 0.001
  cpu_operator_cost              | 0.0025
  cpu_tuple_cost                 | 0.01
  custom_variable_classes        | unset
  data_directory                 | /usr/local/pgsql/data
  DateStyle                      | ISO, MDY
  db_user_namespace              | off
  deadlock_timeout               | 1000
  debug_pretty_print             | off
  debug_print_parse              | off
  debug_print_plan               | off
  debug_print_rewritten          | off
  debug_shared_buffers           | 0
  default_statistics_target      | 10
  default_tablespace             | unset
  default_transaction_isolation  | read committed
  default_transaction_read_only  | off
  default_with_oids              | on
  dynamic_library_path           | $libdir
  effective_cache_size           | 1000
  enable_hashagg                 | on
  enable_hashjoin                | on
  enable_indexscan               | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
  explain_pretty_print           | on
  external_pid_file              | unset
  extra_float_digits             | 0
  from_collapse_limit            | 8
  fsync                          | on
  geqo                           | on
  geqo_effort                    | 5
  geqo_generations               | 0
  geqo_pool_size                 | 0
  geqo_selection_bias            | 2
  geqo_threshold                 | 12
  hba_file                       | /usr/local/pgsql/data/pg_hba.conf
  ident_file                     | /usr/local/pgsql/data/pg_ident.conf
  integer_datetimes              | off
  join_collapse_limit            | 8
  krb_server_keyfile             | unset
  lc_collate                     | C
  lc_ctype                       | C
  lc_messages                    | C
  lc_monetary                    | C
  lc_numeric                     | C
  lc_time                        | C
  listen_addresses               | *
  log_connections                | off
  log_destination                | syslog
  log_directory                  | pg_log
  log_disconnections             | off
  log_duration                   | off
  log_error_verbosity            | default
  log_executor_stats             | off
  log_filename                   | postgresql-%Y-%m-%d_%H%M%S.log
  log_hostname                   | off
  log_line_prefix                | unset
  log_min_duration_statement     | -1
  log_min_error_statement        | panic
  log_min_messages               | notice
  log_parser_stats               | off
  log_planner_stats              | off
  log_rotation_age               | 1440
  log_rotation_size              | 10240
  log_statement                  | none
  log_statement_stats            | off
  log_truncate_on_rotation       | off
  maintenance_work_mem           | 16384
  max_connections                | 40
  max_files_per_process          | 1000
  max_fsm_pages                  | 20000
  max_fsm_relations              | 1000
  max_function_args              | 32
  max_identifier_length          | 63
  max_index_keys                 | 32
  max_locks_per_transaction      | 64
  max_stack_depth                | 2048
  password_encryption            | on
  port                           | 5432
  pre_auth_delay                 | 0
  preload_libraries              | unset
  random_page_cost               | 4
  redirect_stderr                | off
  regex_flavor                   | advanced
  rendezvous_name                | unset
  search_path                    | $user,public
  server_encoding                | LATIN1
  server_version                 | 8.0.4
  shared_buffers                 | 1000
  silent_mode                    | on
  sql_inheritance                | on
  ssl                            | off
  statement_timeout              | 0
  stats_block_level              | off
  stats_command_string           | off
  stats_reset_on_server_start    | on
  stats_row_level                | off
  stats_start_collector          | on
  superuser_reserved_connections | 2
  syslog_facility                | LOCAL0
  syslog_ident                   | postgres
  TimeZone                       | EST5EDT
  trace_notify                   | off
  transaction_isolation          | read committed
  transaction_read_only          | off
  transform_null_equals          | off
  unix_socket_directory          | unset
  unix_socket_group              | unset
  unix_socket_permissions        | 511
  vacuum_cost_delay              | 0
  vacuum_cost_limit              | 200
  vacuum_cost_page_dirty         | 20
  vacuum_cost_page_hit           | 1
  vacuum_cost_page_miss          | 10
  wal_buffers                    | 8
  wal_sync_method                | fsync
  work_mem                       | 1024
  zero_damaged_pages             | off
(139 rows)


Mark Cave-Ayland wrote:
>>-----Original Message-----
>>From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
>>bounces at postgis.refractions.net] On Behalf Of Stephen Woodbridge
>>Sent: 22 November 2005 02:30
>>To: PostGIS Users Discussion
>>Subject: Re: [postgis-users] Query crashed
>>
>>Mark,
>>
>>Here are the EXPLAIN ANALYZE plans for the three query variation. I
>>would love to have your insight on these and how you analyze them as it
>>will probably help me in the future.
>>
>>Thank you,
>>   -Steve W.
> 
> 
> 
> Hi Steve,
> 
> Thank you for the info. It is fairly apparent that the expand() function has
> changed the query plan, but I'll take a look at that later. The first thing
> that stands out is your basic index query below:
> 
> 
> 
>>canada=# EXPLAIN ANALYZE select count(*) from roadseg r, streets s where
>>r.the_geom && s.the_geom;
>>                                                                  QUERY
>>PLAN
>>--------------------------------------------------------------------------
>>-------------------------------------------------------------------
>>  Aggregate  (cost=70651393.10..70651393.10 rows=1 width=0) (actual
>>time=5037110.049..5037110.050 rows=1 loops=1)
>>    ->  Nested Loop  (cost=0.00..70633249.98 rows=7257247 width=0)
>>(actual time=196.615..5027698.511 rows=4795163 loops=1)
>>          ->  Seq Scan on roadseg r  (cost=0.00..139617.69 rows=1843769
>>width=143) (actual time=19.554..13494.148 rows=1843769 loops=1)
>>          ->  Index Scan using streets_gidx on streets s
>>(cost=0.00..38.12 rows=9 width=152) (actual time=1.321..2.705 rows=3
>>loops=1843769)
>>                Index Cond: ("outer".the_geom && s.the_geom)
>>  Total runtime: 5037110.212 ms
>>(6 rows)
>>
>>Time: 5037273.857 ms
> 
> 
> 
> This is doing a nested loop scan so for each of your 1.8m rows in roadseg
> you are extracting 3 rows from streets to give just under 5m rows - but
> extracting these 5m rows is taking over an hour an half which seems a very
> long time indeed.
> 
> Can you confirm your PostgreSQL configuration by doing SHOW ALL and posting
> back the output? My guess is that your shared_buffers setting is far too
> low, although if you let us know the RAM in your database machine then we
> can work out using a "rule of thumb" some new parameters for your
> postgresql.conf file.
> 
> 
> Kind regards,
> 
> Mark.
> 
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
> 
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
> 
> http://www.webbased.co.uk   
> http://www.infomapper.com
> http://www.swtc.co.uk  
> 
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it or
> use it for any purpose nor disclose or distribute its contents to any other
> person.
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 




More information about the postgis-users mailing list