[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