cant get an index scan with a LIKE
От | Greg Caulton |
---|---|
Тема | cant get an index scan with a LIKE |
Дата | |
Msg-id | e44fb6470810061647x6b09cee0q4c2e320e78aff97e@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: cant get an index scan with a LIKE
("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
|
Список | pgsql-performance |
Hi, I have a table sct_descriptions which I have vacuumed, analyzed and reindexed. The index is on term_index INFO: analyzing "public.sct_descriptions" INFO: "sct_descriptions": scanned 3000 of 22861 pages, containing 91877 live rows and 0 dead rows; 3000 rows in sample, 700133 estimated total rows I get an index scan if I do where term_index = select * from sct_descriptions where term_index = 'CHILLS AND FEVER (FINDING)' but I get a sequential scan when I do where term_index like select * from sct_descriptions where term_index like 'CHILLS AND FEVER (FINDING)' This is despite my not using a wildcard, and there being on one row returned in either case. The sequential scan costs 400 ms compared to the index scans 15 ms I changed enable_seqscan = off and put random_page_cost = 0.1 BUT STILL NO USE - IT DOES A SEQUENTIAL SCAN ! Is there anything else I can do? Settings below, this is PostgreSQL 8.3 thanks! Greg "add_missing_from";"off" "allow_system_table_mods";"off" "archive_command";"(disabled)" "archive_mode";"off" "archive_timeout";"0" "array_nulls";"on" "authentication_timeout";"1min" "autovacuum";"on" "autovacuum_analyze_scale_factor";"0.1" "autovacuum_analyze_threshold";"50" "autovacuum_freeze_max_age";"200000000" "autovacuum_max_workers";"3" "autovacuum_naptime";"1min" "autovacuum_vacuum_cost_delay";"20ms" "autovacuum_vacuum_cost_limit";"-1" "autovacuum_vacuum_scale_factor";"0.2" "autovacuum_vacuum_threshold";"50" "backslash_quote";"safe_encoding" "bgwriter_delay";"200ms" "bgwriter_lru_maxpages";"100" "bgwriter_lru_multiplier";"2" "block_size";"8192" "bonjour_name";"" "check_function_bodies";"on" "checkpoint_completion_target";"0.5" "checkpoint_segments";"3" "checkpoint_timeout";"5min" "checkpoint_warning";"30s" "client_encoding";"UNICODE" "client_min_messages";"notice" "commit_delay";"0" "commit_siblings";"5" "config_file";"C:/Program Files/PostgreSQL/8.3/data/postgresql.conf" "constraint_exclusion";"off" "cpu_index_tuple_cost";"0.005" "cpu_operator_cost";"0.0025" "cpu_tuple_cost";"0.01" "custom_variable_classes";"" "data_directory";"C:/Program Files/PostgreSQL/8.3/data" "DateStyle";"ISO, MDY" "db_user_namespace";"off" "deadlock_timeout";"1s" "debug_assertions";"off" "debug_pretty_print";"off" "debug_print_parse";"off" "debug_print_plan";"off" "debug_print_rewritten";"off" "default_statistics_target";"10" "default_tablespace";"" "default_text_search_config";"pg_catalog.english" "default_transaction_isolation";"read committed" "default_transaction_read_only";"off" "default_with_oids";"off" "dynamic_library_path";"$libdir" "effective_cache_size";"128MB" "enable_bitmapscan";"on" "enable_hashagg";"on" "enable_hashjoin";"on" "enable_indexscan";"on" "enable_mergejoin";"on" "enable_nestloop";"on" "enable_seqscan";"off" "enable_sort";"on" "enable_tidscan";"on" "escape_string_warning";"on" "explain_pretty_print";"on" "external_pid_file";"" "extra_float_digits";"0" "from_collapse_limit";"8" "fsync";"on" "full_page_writes";"on" "geqo";"on" "geqo_effort";"5" "geqo_generations";"0" "geqo_pool_size";"0" "geqo_selection_bias";"2" "geqo_threshold";"12" "gin_fuzzy_search_limit";"0" "hba_file";"C:/Program Files/PostgreSQL/8.3/data/pg_hba.conf" "ident_file";"C:/Program Files/PostgreSQL/8.3/data/pg_ident.conf" "ignore_system_indexes";"off" "integer_datetimes";"off" "join_collapse_limit";"8" "krb_caseins_users";"off" "krb_realm";"" "krb_server_hostname";"" "krb_server_keyfile";"" "krb_srvname";"postgres" "lc_collate";"English_United States.1252" "lc_ctype";"English_United States.1252" "lc_messages";"English_United States" "lc_monetary";"English_United States" "lc_numeric";"English_United States" "lc_time";"English_United States" "listen_addresses";"localhost" "local_preload_libraries";"" "log_autovacuum_min_duration";"-1" "log_checkpoints";"off" "log_connections";"off" "log_destination";"stderr" "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";"%t " "log_lock_waits";"off" "log_min_duration_statement";"-1" "log_min_error_statement";"error" "log_min_messages";"notice" "log_parser_stats";"off" "log_planner_stats";"off" "log_rotation_age";"1d" "log_rotation_size";"10MB" "log_statement";"none" "log_statement_stats";"off" "log_temp_files";"-1" "log_timezone";"US/Eastern" "log_truncate_on_rotation";"off" "logging_collector";"on" "maintenance_work_mem";"16MB" "max_connections";"100" "max_files_per_process";"1000" "max_fsm_pages";"204800" "max_fsm_relations";"1000" "max_function_args";"100" "max_identifier_length";"63" "max_index_keys";"32" "max_locks_per_transaction";"64" "max_prepared_transactions";"5" "max_stack_depth";"2MB" "password_encryption";"on" "port";"5432" "post_auth_delay";"0" "pre_auth_delay";"0" "random_page_cost";"0.1" "regex_flavor";"advanced" "search_path";""$user",public" "seq_page_cost";"1" "server_encoding";"UTF8" "server_version";"8.3.1" "server_version_num";"80301" "session_replication_role";"origin" "shared_buffers";"32MB" "shared_preload_libraries";"$libdir/plugins/plugin_debugger.dll" "silent_mode";"off" "sql_inheritance";"on" "ssl";"off" "ssl_ciphers";"ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH" "standard_conforming_strings";"off" "statement_timeout";"0" "superuser_reserved_connections";"3" "synchronize_seqscans";"on" "synchronous_commit";"on" "tcp_keepalives_count";"0" "tcp_keepalives_idle";"0" "tcp_keepalives_interval";"0" "temp_buffers";"1024" "temp_tablespaces";"" "TimeZone";"US/Eastern" "timezone_abbreviations";"Default" "trace_notify";"off" "trace_sort";"off" "track_activities";"on" "track_counts";"on" "transaction_isolation";"read committed" "transaction_read_only";"off" "transform_null_equals";"off" "unix_socket_directory";"" "unix_socket_group";"" "unix_socket_permissions";"511" "update_process_title";"on" "vacuum_cost_delay";"0" "vacuum_cost_limit";"200" "vacuum_cost_page_dirty";"20" "vacuum_cost_page_hit";"1" "vacuum_cost_page_miss";"10" "vacuum_freeze_min_age";"100000000" "wal_buffers";"64kB" "wal_sync_method";"open_datasync" "wal_writer_delay";"200ms" "work_mem";"1MB" "xmlbinary";"base64" "xmloption";"content" "zero_damaged_pages";"off"
В списке pgsql-performance по дате отправления: