cant get an index scan with a LIKE - Mailing list 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"