This is probably a common problem, but I couldn't really find a direct answer in the archives (or maybe just couldn't find one that satisfied me ;-). I created an index specifically to help a query and the optimizer does not use it. It prefers an older index that has one less column, but that yields much poorer performance.
The query get the smallest date in a period, for some criteria. The one table used in the query has 604494 rows. The index I created to try to optimize it is "xie3cham_servico", and has all the three columns of the table that are used in the query. But the index being used is actually xie2cham_servico (as can be seen below) which has only two columns, one of them completely useless for the query! I'm probably missing some setting, but I couldn't figure out which.
One last comment, if I test this on a server all by myself, after some test (when, I imagine, all related disk pages are on cache) the time difference between the two situations is much smaller (4secs against 2 secs), but, of course, this doesn't help me much ...
Here is the associated information:
Table "public.cham_servico"
Column | Type | Modifiers
------------------+--------------------------------+-----------
dt_inicial | timestamp(0) without time zone | not null
cod_bxs | integer | not null
chave | integer | not null
identificacao | integer | not null
cod_cifra | character varying(25) | not null
dt_identificacao | timestamp(0) without time zone | not null
dt_ocupacao | timestamp(0) without time zone |
temp_troca | integer |
temp_ura | integer |
temp_filapa | integer |
temp_atendpa | integer |
temp_total | integer |
dt_finalizacao | timestamp(0) without time zone |
pa | integer |
flg_tipodiscagem | character(1) |
cod_liberservico | integer |
dt_login | timestamp(0) without time zone |
id_chave_grupo | character varying(20) |
Indexes: xpkcham_servico primary key btree (dt_inicial, cod_bxs, chave, identificacao, cod_cifra, dt_identificacao),
xie1cham_servico btree (dt_finalizacao, cod_cifra),
xie2cham_servico btree (id_chave_grupo, dt_inicial),
xie3cham_servico btree (id_chave_grupo, cod_bxs, dt_finalizacao)
Foreign Key constraints: rchamadaxservico FOREIGN KEY (dt_inicial, cod_bxs, chave, identificacao) REFERENCES cham_chamada(dt_inicial, cod_bxs, chave, identificacao) ON UPDATE NO ACTION ON DELETE NO ACTION
explain analyze SELECT dt_finalizacao FROM cham_servico WHERE id_chave_grupo = '7458' AND cod_bxs = 1 AND dt_finalizacao > to_timestamp ('2004-04-20','YYYY-MM-DD') AND dt_finalizacao < now() ORDER BY dt_finalizacao ASC LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=15663.77..15663.77 rows=1 width=8) (actual time=14002.66..14002.66 rows=1 loops=1)
-> Sort (cost=15663.77..15663.83 rows=26 width=8) (actual time=14002.65..14002.65 rows=2 loops=1)
Sort Key: dt_finalizacao
-> Index Scan using xie2cham_servico on cham_servico (cost=0.00..15663.15 rows=26 width=8) (actual time=13604.37..13980.16 rows=3173 loops=1)
Index Cond: (id_chave_grupo = '7458'::character varying)
Filter: ((cod_bxs = 1) AND ((dt_finalizacao)::timestamp with time zone > to_timestamp('2004-04-20'::text, 'YYYY-MM-DD'::text)) AND ((dt_finalizacao)::timestamp with time zone < now()))
Total runtime: 45014.06 msec
(7 rows)
Time: 45105,73 ms
If I "drop index xie2cham_servico;", I get :
explain analyze SELECT dt_finalizacao FROM cham_servico WHERE id_chave_grupo = '7458' AND cod_bxs = 1 AND dt_finalizacao > to_timestamp ('2004-04-20','YYYY-MM-DD') AND dt_finalizacao < now() ORDER BY dt_finalizacao ASC LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=17600.92..17600.92 rows=1 width=8) (actual time=8750.64..8750.64 rows=1 loops=1)
-> Sort (cost=17600.92..17600.99 rows=26 width=8) (actual time=8750.63..8750.63 rows=2 loops=1)
Sort Key: dt_finalizacao
-> Index Scan using xie3cham_servico on cham_servico (cost=0.00..17600.30 rows=26 width=8) (actual time=8432.37..8743.29 rows=3176 loops=1)
Index Cond: ((id_chave_grupo = '7458'::character varying) AND (cod_bxs = 1))
Filter: (((dt_finalizacao)::timestamp with time zone > to_timestamp('2004-04-20'::text, 'YYYY-MM-DD'::text)) AND ((dt_finalizacao)::timestamp with time zone < now()))
Total runtime: 8751.50 msec
(7 rows)
Time: 8758,36 ms
Some more environment info:
Postgres 7.3.4 on Linux Redhat 9.
show all:
name | setting --------------------------------+--------------------------------------- australian_timezones | off authentication_timeout | 60 autocommit | on checkpoint_segments | 3 checkpoint_timeout | 300 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 DateStyle | ISO with US (NonEuropean) conventions db_user_namespace | off deadlock_timeout | 1000 debug_pretty_print | off debug_print_parse | off debug_print_plan | off debug_print_rewritten | off default_statistics_target | 10 default_transaction_isolation | read committed dynamic_library_path | $libdir effective_cache_size | 1000 enable_hashjoin | on enable_indexscan | on enable_mergejoin | on enable_nestloop | on enable_seqscan | off enable_sort | on enable_tidscan | on explain_pretty_print | on fixbtree | on fsync | on geqo | on geqo_effort | 1 geqo_generations | 0 geqo_pool_size | 0 geqo_random_seed | -1 geqo_selection_bias | 2 geqo_threshold | 11 hostname_lookup | off krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab lc_messages | pt_BR lc_monetary | pt_BR lc_numeric | pt_BR lc_time | pt_BR log_connections | off log_duration | off log_min_error_statement | panic log_pid | off log_statement | off log_timestamp | off max_connections | 32 max_expr_depth | 10000 max_files_per_process | 1000 max_fsm_pages | 10000 max_fsm_relations | 1000 max_locks_per_transaction | 64 password_encryption | on port | 5432 pre_auth_delay | 0 random_page_cost | 4 search_path | $user,public server_encoding | SQL_ASCII server_min_messages | notice shared_buffers | 64 show_executor_stats | off show_parser_stats | off show_planner_stats | off show_source_port | off show_statement_stats | off silent_mode | off sort_mem | 1024 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 | 0 syslog_facility | LOCAL0 syslog_ident | postgres tcpip_socket | on TimeZone | unknown trace_notify | off TRANSACTION ISOLATION LEVEL | READ COMMITTED transform_null_equals | off unix_socket_directory | unset unix_socket_group | unset unix_socket_permissions | 511 vacuum_mem | 8192 virtual_host | unset wal_buffers | 8 wal_debug | 0 wal_sync_method | fdatasync zero_damaged_pages | off
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
