Hello,
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_servi co", and has all the three columns of the table that are used in
the query. But the index being used is actually xie2cham_servic o (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_se rvico"
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_identificaca o | 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_tipodiscage m | character(1) |
cod_liberservic o | 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_identificaca o),
xie1cham_servic o btree (dt_finalizacao , cod_cifra),
xie2cham_servic o btree (id_chave_grupo , dt_inicial),
xie3cham_servic o btree (id_chave_grupo , cod_bxs, dt_finalizacao)
Foreign Key constraints: rchamadaxservic o 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_servic o on cham_servico
(cost=0.00..156 63.15 rows=26 width=8) (actual time=13604.37.. 13980.16 rows=3173
loops=1)
Index Cond: (id_chave_grupo = '7458'::charact er varying)
Filter: ((cod_bxs = 1) AND ((dt_finalizaca o)::timestamp with time
zone > to_timestamp('2 004-04-20'::text, 'YYYY-MM-DD'::text)) AND
((dt_finalizaca o)::timestamp with time zone < now()))
Total runtime: 45014.06 msec
(7 rows)
Time: 45105,73 ms
If I "drop index xie2cham_servic o;", 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..8 750.64
rows=1 loops=1)
-> Sort (cost=17600.92. .17600.99 rows=26 width=8) (actual
time=8750.63..8 750.63 rows=2 loops=1)
Sort Key: dt_finalizacao
-> Index Scan using xie3cham_servic o on cham_servico
(cost=0.00..176 00.30 rows=26 width=8) (actual time=8432.37..8 743.29 rows=3176
loops=1)
Index Cond: ((id_chave_grup o = '7458'::charact er varying) AND
(cod_bxs = 1))
Filter: (((dt_finalizac ao)::timestamp with time zone >
to_timestamp('2 004-04-20'::text, 'YYYY-MM-DD'::text)) AND
((dt_finalizaca o)::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_time zones | off
authentication_ timeout | 60
autocommit | on
checkpoint_segm ents | 3
checkpoint_time out | 300
client_encoding | SQL_ASCII
client_min_mess ages | notice
commit_delay | 0
commit_siblings | 5
cpu_index_tuple _cost | 0.001
cpu_operator_co st | 0.0025
cpu_tuple_cost | 0.01
DateStyle | ISO with US (NonEuropean) conventions
db_user_namespa ce | off
deadlock_timeou t | 1000
debug_pretty_pr int | off
debug_print_par se | off
debug_print_pla n | off
debug_print_rew ritten | off
default_statist ics_target | 10
default_transac tion_isolation | read committed
dynamic_library _path | $libdir
effective_cache _size | 1000
enable_hashjoin | on
enable_indexsca n | on
enable_mergejoi n | 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_generation s | 0
geqo_pool_size | 0
geqo_random_see d | -1
geqo_selection_ bias | 2
geqo_threshold | 11
hostname_lookup | off
krb_server_keyf ile | 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_s tatement | panic
log_pid | off
log_statement | off
log_timestamp | off
max_connections | 32
max_expr_depth | 10000
max_files_per_p rocess | 1000
max_fsm_pages | 10000
max_fsm_relatio ns | 1000
max_locks_per_t ransaction | 64
password_encryp tion | on
port | 5432
pre_auth_delay | 0
random_page_cos t | 4
search_path | $user,public
server_encoding | SQL_ASCII
server_min_mess ages | notice
shared_buffers | 64
show_executor_s tats | off
show_parser_sta ts | off
show_planner_st ats | off
show_source_por t | off
show_statement_ stats | off
silent_mode | off
sort_mem | 1024
sql_inheritance | on
ssl | off
statement_timeo ut | 0
stats_block_lev el | off
stats_command_s tring | off
stats_reset_on_ server_start | on
stats_row_level | off
stats_start_col lector | on
superuser_reser ved_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_dir ectory | unset
unix_socket_gro up | unset
unix_socket_per missions | 511
vacuum_mem | 8192
virtual_host | unset
wal_buffers | 8
wal_debug | 0
wal_sync_method | fdatasync
zero_damaged_pa ges | off
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
