On Thu, 2005-11-10 at 12:23 -0500, Tom Lane wrote: > Apparently, you are using a platform and/or locale in which strcoll() is > spectacularly, god-awfully slow --- on the order of 10 msec per comparison.
The version with the condition is definitely doing more I/O. The version without the condition doesn't read at all. I strace'd an explain analyze for each separately, and this is what I ended up with (the first is with the condition, the second is without): bash-2.05b$ cut '-d(' -f1 subsourcestrace | sort | uniq -c 7127 gettimeofday 75213 _llseek 1 Process 30227 attached - interrupt to quit 1 Process 30227 detached 148671 read 2 recv 4 semop 4 send bash-2.05b$ cut '-d(' -f1 subsourcestrace-nocond | sort | uniq -c 9103 gettimeofday 7 _llseek 1 Process 30227 attached - interrupt to quit 1 Process 30227 detached 2 recv 4 send For the moment, all of the rows in the view I'm selecting from satisfy the condition, so the output of both queries is the same. The relevant rows of the underlying tables are probably pretty contiguous (all of the rows satisfying the condition and the join were inserted at the same time). Could it just be the result of a weird physical distribution of data in the table/index files? For the fast query, the actual number of rows is a lot less than the planner expects. > This is a bit hard to believe but I can't make sense of those numbers > any other way. What is the platform exactly, and what database locale > and encoding are you using? It's RHEL 3 on x86: [EMAIL PROTECTED] root]# uname -a Linux rehoboam 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:52:23 EDT 2005 i686 i686 i386 GNU/Linux The glibc version is 2.3.2. statgen=# select current_setting('lc_collate'); current_setting ----------------- en_US.UTF-8 Not sure what's relevant, but here's some more info: The machine has 4.5GiB of RAM and a 5-disk Raid 5. It's a dual xeon 3.2ghz. relname | relpages | reltuples -----------------------------+----------+------------- external_id_map | 126883 | 1.55625e+07 external_id_map_primary_key | 64607 | 1.55625e+07 subject | 31 | 1186 subject_pkey | 19 | 1186 I've attached the output of "select name, setting from pg_settings". And, in case my original message isn't handy, the explain analyze output and table/view info is below. Thanks for taking a look, Mitch statgen=> explain analyze select * from subject_source; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Merge Join (cost=0.00..330.72 rows=1186 width=46) (actual time=0.051..8.890 rows=1186 loops=1) Merge Cond: ("outer".id = "inner".target_id) -> Index Scan using subject_pkey on subject norm (cost=0.00..63.36 rows=1186 width=28) (actual time=0.022..1.441 rows=1186 loops=1) -> Index Scan using external_id_map_primary_key on external_id_map eim (cost=0.00..2485226.70 rows=15562513 width=26) (actual time=0.016..2.532 rows=2175 loops=1) Total runtime: 9.592 ms (5 rows) statgen=> explain analyze select * from subject_source where source='SCH'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Merge Join (cost=0.00..1147.33 rows=1 width=46) (actual time=0.054..20258.161 rows=1186 loops=1) Merge Cond: ("outer".id = "inner".target_id) -> Index Scan using subject_pkey on subject norm (cost=0.00..63.36 rows=1186 width=28) (actual time=0.022..1.478 rows=1186 loops=1) -> Index Scan using external_id_map_primary_key on external_id_map eim (cost=0.00..2524132.99 rows=2335 width=26) (actual time=0.022..20252.326 rows=1186 loops=1) Filter: (source = 'SCH'::bpchar) Total runtime: 20258.922 ms (6 rows) statgen=> \d subject_source View "public.subject_source" Column | Type | Modifiers -----------+-----------------------+----------- id | bigint | sex | integer | parent1 | bigint | parent2 | bigint | source | character(3) | source_id | character varying(32) | View definition: SELECT norm.id, norm.sex, norm.parent1, norm.parent2, eim.source, eim.source_id FROM subject norm JOIN util.external_id_map eim ON norm.id = eim.target_id; statgen=> \d subject Table "public.subject" Column | Type | Modifiers ---------+---------+----------- id | bigint | not null sex | integer | parent1 | bigint | parent2 | bigint | Indexes: "subject_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "subject_parent1" FOREIGN KEY (parent1) REFERENCES subject(id) DEFERRABLE INITIALLY DEFERRED "subject_parent2" FOREIGN KEY (parent2) REFERENCES subject(id) DEFERRABLE INITIALLY DEFERRED "subject_id_map" FOREIGN KEY (id) REFERENCES util.external_id_map(target_id) DEFERRABLE INITIALLY DEFERRED statgen=> \d util.external_id_map Table "util.external_id_map" Column | Type | Modifiers -----------+-----------------------+----------- source_id | character varying(32) | not null source | character(3) | not null target_id | bigint | not null Indexes: "external_id_map_primary_key" PRIMARY KEY, btree (target_id) "external_id_map_source_source_id_unique" UNIQUE, btree (source, source_id) "external_id_map_source" btree (source) "external_id_map_source_target_id" btree (source, target_id) Foreign-key constraints: "external_id_map_source" FOREIGN KEY (source) REFERENCES util.source(id)
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 | 32 checkpoint_timeout | 300 checkpoint_warning | 60 client_encoding | SQL_ASCII client_min_messages | notice commit_delay | 0 commit_siblings | 5 cpu_index_tuple_cost | 0.001 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 custom_variable_classes | unset 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 | off effective_cache_size | 65536 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 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 integer_datetimes | off join_collapse_limit | 8 lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 listen_addresses | * log_connections | off log_destination | stderr log_disconnections | off log_duration | off log_error_verbosity | default log_executor_stats | off 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 | 0 log_statement | none log_statement_stats | off log_truncate_on_rotation | on maintenance_work_mem | 65536 max_connections | 100 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 random_page_cost | 4 redirect_stderr | on regex_flavor | advanced rendezvous_name | unset search_path | $user,public server_encoding | SQL_ASCII server_version | 8.0.3 shared_buffers | 32768 silent_mode | off sql_inheritance | on ssl | off statement_timeout | 0 stats_block_level | off stats_command_string | on 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 | PST8PDT trace_notify | off transaction_isolation | read committed transaction_read_only | off transform_null_equals | off 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 | fdatasync work_mem | 65536 zero_damaged_pages | off (128 rows)
---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match