X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 53D49D1B50F for ; Tue, 27 Apr 2004 17:22:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 08751-04 for ; Tue, 27 Apr 2004 17:22:27 -0300 (ADT) Received: from mail.digitro.com.br (unknown [200.247.100.135]) by svr1.postgresql.org (Postfix) with ESMTP id 6C3DED1B8D5 for ; Tue, 27 Apr 2004 17:22:22 -0300 (ADT) Received: from serverdgt.daf.digitro.com.br (ias.digitro.com.br [200.247.100.134]) by mail.digitro.com.br (8.12.5/linuxconf) with ESMTP id i3RKMQEl025542 for ; Tue, 27 Apr 2004 17:22:26 -0300 Received: from dgtat ([192.168.160.169]) by serverdgt.daf.digitro.com.br (8.12.5/linuxconf) with SMTP id i3RKM7ZL022624 for ; Tue, 27 Apr 2004 17:22:07 -0300 MIME-Version: 1.0 Message-Id: <408EC0CB.00000B.00684@dgtat> Date: Tue, 27 Apr 2004 17:21:31 -0300 (Hora oficial do Brasil) Content-Type: Multipart/related; type="multipart/alternative"; boundary="------------Boundary-00=_V7JUCJD0000000000000" X-Mailer: IncrediMail 2001 (1750690) From: "Eduardo Naschenweng" X-FID: FLAVOR00-NONE-0000-0000-000000000000 X-FVER: X-CNT: ; X-Priority: 3 To: Subject: Optimizer choosing smaller index instead of right one X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=3.4 tagged_above=0.0 required=5.0 tests=CLICK_BELOW, DNS_FROM_RFCI_DSN, HTML_LINK_CLICK_HERE, HTML_MESSAGE, RCVD_IN_NJABL, RCVD_IN_NJABL_SPAM, RCVD_IN_SORBS, RCVD_IN_SORBS_HTTP X-Spam-Level: *** X-Archive-Number: 200404/326 X-Sequence-Number: 13299 --------------Boundary-00=_V7JUCJD0000000000000 Content-Type: Multipart/Alternative; boundary="------------Boundary-00=_V7JU7TH0000000000000" --------------Boundary-00=_V7JU7TH0000000000000 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello,=0D =0D 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= =20 -). 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.=0D =0D 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 (I would expect that postgres wouldn't even have to go to the table to solve 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.=0D =0D 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 does not help me much ...=0D =0D Here is the associated information:=0D =0D Table "public.cham_servico"=0D Column | Type | Modifiers =0D ------------------+--------------------------------+-----------=0D dt_inicial | timestamp(0) without time zone | not null=0D cod_bxs | integer | not null=0D chave | integer | not null=0D identificacao | integer | not null=0D cod_cifra | character varying(25) | not null=0D dt_identificacao | timestamp(0) without time zone | not null=0D dt_ocupacao | timestamp(0) without time zone | =0D temp_troca | integer | =0D temp_ura | integer | =0D temp_filapa | integer | =0D temp_atendpa | integer | =0D temp_total | integer | =0D dt_finalizacao | timestamp(0) without time zone | =0D pa | integer | =0D flg_tipodiscagem | character(1) | =0D cod_liberservico | integer | =0D dt_login | timestamp(0) without time zone | =0D id_chave_grupo | character varying(20) | =0D Indexes: xpkcham_servico primary key btree (dt_inicial, cod_bxs, chave, identificacao, cod_cifra, dt_identificacao),=0D xie1cham_servico btree (dt_finalizacao, cod_cifra),=0D xie2cham_servico btree (id_chave_grupo, dt_inicial),=0D xie3cham_servico btree (id_chave_grupo, cod_bxs, dt_finalizacao)=0D 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=0D =0D explain analyze SELECT dt_finalizacao FROM cham_servico WHERE id_chave_grupo =3D '7458' AND cod_bxs =3D 1 AND dt_finalizacao > to_timestamp ('2004-04-20= ', YYYY-MM-DD') AND dt_finalizacao < now() ORDER BY dt_finalizacao ASC LIMIT 1= ;=0D =0D QUERY PLAN =0D ---------------------------------------------------------------------------= -- ---------------------------------------------------------------------------= -- --------------------------------------------=0D Limit (cost=3D15663.77..15663.77 rows=3D1 width=3D8) (actual time=3D14002.6= 6..14002 66 rows=3D1 loops=3D1)=0D -> Sort (cost=3D15663.77..15663.83 rows=3D26 width=3D8) (actual time=3D1400= 2.65. 14002.65 rows=3D2 loops=3D1)=0D Sort Key: dt_finalizacao=0D -> Index Scan using xie2cham_servico on cham_servico (cost=3D0.00..15663.15 rows=3D26 width=3D8) (actual time=3D13604.37..13980.16 rows=3D3173 loops=3D= 1)=0D Index Cond: (id_chave_grupo =3D '7458'::character varying)=0D Filter: ((cod_bxs =3D 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()))=0D Total runtime: 45014.06 msec=0D (7 rows)=0D =0D Time: 45105,73 ms=0D =0D If I "drop index xie2cham_servico;", I get :=0D =0D explain analyze SELECT dt_finalizacao FROM cham_servico WHERE id_chave_grupo =3D '7458' AND cod_bxs =3D 1 AND dt_finalizacao > to_timestamp ('2004-04-20= ', YYYY-MM-DD') AND dt_finalizacao < now() ORDER BY dt_finalizacao ASC LIMIT 1= ;=0D QUERY PLAN =0D ---------------------------------------------------------------------------= -- ---------------------------------------------------------------------------= -- --------------------------=0D Limit (cost=3D17600.92..17600.92 rows=3D1 width=3D8) (actual time=3D8750.64= ..8750.64 rows=3D1 loops=3D1)=0D -> Sort (cost=3D17600.92..17600.99 rows=3D26 width=3D8) (actual time=3D8750= .63..8750 63 rows=3D2 loops=3D1)=0D Sort Key: dt_finalizacao=0D -> Index Scan using xie3cham_servico on cham_servico (cost=3D0.00..17600.30 rows=3D26 width=3D8) (actual time=3D8432.37..8743.29 rows=3D3176 loops=3D1)= =0D Index Cond: ((id_chave_grupo =3D '7458'::character varying) AND (cod_bxs = =3D 1))=0D 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()))=0D Total runtime: 8751.50 msec=0D (7 rows)=0D =0D Time: 8758,36 ms=0D =0D Some more environment info:=0D =0D Postgres 7.3.4 on Linux Redhat 9.=0D =0D show all:=0D =0D name | setting =0D --------------------------------+---------------------------------------=0D australian_timezones | off=0D authentication_timeout | 60=0D autocommit | on=0D checkpoint_segments | 3=0D checkpoint_timeout | 300=0D client_encoding | SQL_ASCII=0D client_min_messages | notice=0D commit_delay | 0=0D commit_siblings | 5=0D cpu_index_tuple_cost | 0.001=0D cpu_operator_cost | 0.0025=0D cpu_tuple_cost | 0.01=0D DateStyle | ISO with US (NonEuropean) conventions=0D db_user_namespace | off=0D deadlock_timeout | 1000=0D debug_pretty_print | off=0D debug_print_parse | off=0D debug_print_plan | off=0D debug_print_rewritten | off=0D default_statistics_target | 10=0D default_transaction_isolation | read committed=0D dynamic_library_path | $libdir=0D effective_cache_size | 1000=0D enable_hashjoin | on=0D enable_indexscan | on=0D enable_mergejoin | on=0D enable_nestloop | on=0D enable_seqscan | off=0D enable_sort | on=0D enable_tidscan | on=0D explain_pretty_print | on=0D fixbtree | on=0D fsync | on=0D geqo | on=0D geqo_effort | 1=0D geqo_generations | 0=0D geqo_pool_size | 0=0D geqo_random_seed | -1=0D geqo_selection_bias | 2=0D geqo_threshold | 11=0D hostname_lookup | off=0D krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab=0D lc_messages | pt_BR=0D lc_monetary | pt_BR=0D lc_numeric | pt_BR=0D lc_time | pt_BR=0D log_connections | off=0D log_duration | off=0D log_min_error_statement | panic=0D log_pid | off=0D log_statement | off=0D log_timestamp | off=0D max_connections | 32=0D max_expr_depth | 10000=0D max_files_per_process | 1000=0D max_fsm_pages | 10000=0D max_fsm_relations | 1000=0D max_locks_per_transaction | 64=0D password_encryption | on=0D port | 5432=0D pre_auth_delay | 0=0D random_page_cost | 4=0D search_path | $user,public=0D server_encoding | SQL_ASCII=0D server_min_messages | notice=0D shared_buffers | 64=0D show_executor_stats | off=0D show_parser_stats | off=0D show_planner_stats | off=0D show_source_port | off=0D show_statement_stats | off=0D silent_mode | off=0D sort_mem | 1024=0D sql_inheritance | on=0D ssl | off=0D statement_timeout | 0=0D stats_block_level | off=0D stats_command_string | off=0D stats_reset_on_server_start | on=0D stats_row_level | off=0D stats_start_collector | on=0D superuser_reserved_connections | 2=0D syslog | 0=0D syslog_facility | LOCAL0=0D syslog_ident | postgres=0D tcpip_socket | on=0D TimeZone | unknown=0D trace_notify | off=0D TRANSACTION ISOLATION LEVEL | READ COMMITTED=0D transform_null_equals | off=0D unix_socket_directory | unset=0D unix_socket_group | unset=0D unix_socket_permissions | 511=0D vacuum_mem | 8192=0D virtual_host | unset=0D wal_buffers | 8=0D wal_debug | 0=0D wal_sync_method | fdatasync=0D zero_damaged_pages | off=0D =0D =0D =2E --------------Boundary-00=_V7JU7TH0000000000000 Content-Type: Text/HTML; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0D
Hello,

This is probably a common problem, but I couldn't= =20 really find a direct answer in the archives (or maybe just couldn't f= ind=20 one that satisfied me ;-). I created an index specifically to help a = query=20 and the optimizer does not use it. It prefers an older index that has= one=20 less column, but that yields much poorer performance.

The quer= y get=20 the smallest date in a period, for some criteria. The one table used = in=20 the query has 604494 rows. The index I created to try to optimize it = is=20 "xie3cham_servico", and has all the three columns of the table that a= re=20 used in the query (I would expect that postgres wouldn't even have to= go=20 to the table to solve the query). But the index being used is actuall= y=20 xie2cham_servico (as can be seen below) which has only two columns, o= ne of=20 them completely useless for the query! I'm probably missing some sett= ing,=20 but I couldn't figure out which.

One last comment, if I test t= his=20 on a server all by myself, after some test (when, I imagine, all rela= ted=20 disk pages are on cache) the time difference between the two situatio= ns is=20 much smaller (4secs against 2 secs), but, of course, this does not he= lp me=20 much ...

Here is the associated information:

Table=20 "public.cham_servico"
Column | Type | Modifiers=20
------------------+--------------------------------+-----------dt_inicial=20 | timestamp(0) without time zone | not null
cod_bxs | integer | no= t=20 null
chave | integer | not null
identificacao | integer | not= =20 null
cod_cifra | character varying(25) | not null
dt_identifica= cao |=20 timestamp(0) without time zone | not null
dt_ocupacao | timestamp(= 0)=20 without time zone |
temp_troca | integer |
temp_ura | integer= |=20
temp_filapa | integer |
temp_atendpa | integer |
temp_tot= al |=20 integer |
dt_finalizacao | timestamp(0) without time zone |
p= a |=20 integer |
flg_tipodiscagem | character(1) |
cod_liberservico = |=20 integer |
dt_login | timestamp(0) without time zone |=20
id_chave_grupo | character varying(20) |
Indexes: xpkcham_ser= vico=20 primary key btree (dt_inicial, cod_bxs, chave, identificacao, cod_cif= ra,=20 dt_identificacao),
xie1cham_servico btree (dt_finalizacao,=20 cod_cifra),
xie2cham_servico btree (id_chave_grupo,=20 dt_inicial),
xie3cham_servico btree (id_chave_grupo, cod_bxs,=20 dt_finalizacao)
Foreign Key constraints: rchamadaxservico FOREIGN = KEY=20 (dt_inicial, cod_bxs, chave, identificacao) REFERENCES=20 cham_chamada(dt_inicial, cod_bxs, chave, identificacao) ON UPDATE NO= =20 ACTION ON DELETE NO ACTION

explain analyze SELECT dt_finalizac= ao=20 FROM cham_servico WHERE id_chave_grupo =3D '7458' AND cod_bxs =3D 1 A= ND=20 dt_finalizacao > to_timestamp ('2004-04-20','YYYY-MM-DD') AND=20 dt_finalizacao < now() ORDER BY dt_finalizacao ASC LIMIT=20 1;

QUERY PLAN=20
-----------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------
Limit=20 (cost=3D15663.77..15663.77 rows=3D1 width=3D8) (actual time=3D14002.6= 6..14002.66=20 rows=3D1 loops=3D1)
-> Sort (cost=3D15663.77..15663.83 rows=3D2= 6 width=3D8)=20 (actual time=3D14002.65..14002.65 rows=3D2 loops=3D1)
Sort Key:=20 dt_finalizacao
-> Index Scan using xie2cham_servico on cham_ser= vico=20 (cost=3D0.00..15663.15 rows=3D26 width=3D8) (actual time=3D13604.37..= 13980.16=20 rows=3D3173 loops=3D1)
Index Cond: (id_chave_grupo =3D '7458'::cha= racter=20 varying)
Filter: ((cod_bxs =3D 1) AND ((dt_finalizacao)::timestamp= with=20 time zone > to_timestamp('2004-04-20'::text, 'YYYY-MM-DD'::text)) = AND=20 ((dt_finalizacao)::timestamp with time zone < now()))
Total run= time:=20 45014.06 msec
(7 rows)

Time: 45105,73 ms

If I "drop = index=20 xie2cham_servico;", I get :

explain analyze SELECT dt_finaliza= cao=20 FROM cham_servico WHERE id_chave_grupo =3D '7458' AND cod_bxs =3D 1 A= ND=20 dt_finalizacao > to_timestamp ('2004-04-20','YYYY-MM-DD') AND=20 dt_finalizacao < now() ORDER BY dt_finalizacao ASC LIMIT 1;
QUE= RY=20 PLAN=20
-----------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------
Limit=20 (cost=3D17600.92..17600.92 rows=3D1 width=3D8) (actual time=3D8750.64= ..8750.64=20 rows=3D1 loops=3D1)
-> Sort (cost=3D17600.92..17600.99 rows=3D2= 6 width=3D8)=20 (actual time=3D8750.63..8750.63 rows=3D2 loops=3D1)
Sort Key:=20 dt_finalizacao
-> Index Scan using xie3cham_servico on cham_ser= vico=20 (cost=3D0.00..17600.30 rows=3D26 width=3D8) (actual time=3D8432.37..8= 743.29=20 rows=3D3176 loops=3D1)
Index Cond: ((id_chave_grupo =3D '7458'::ch= aracter=20 varying) AND (cod_bxs =3D 1))
Filter: (((dt_finalizacao)::timestam= p with=20 time zone > to_timestamp('2004-04-20'::text, 'YYYY-MM-DD'::text)) = AND=20 ((dt_finalizacao)::timestamp with time zone < now()))
Total run= time:=20 8751.50 msec
(7 rows)

Time: 8758,36 ms

Some more=20 environment info:

Postgres 7.3.4 on Linux Redhat 9.

sho= w=20 all:

name | setting=20
--------------------------------+--------------------------------= -------
australian_timezones=20 | off
authentication_timeout | 60
autocommit |=20 on
checkpoint_segments | 3
checkpoint_timeout |=20 300
client_encoding | SQL_ASCII
client_min_messages |=20 notice
commit_delay | 0
commit_siblings | 5
cpu_index_tuple_= cost=20 | 0.001
cpu_operator_cost | 0.0025
cpu_tuple_cost |=20 0.01
DateStyle | ISO with US (NonEuropean)=20 conventions
db_user_namespace | off
deadlock_timeout |=20 1000
debug_pretty_print | off
debug_print_parse |=20 off
debug_print_plan | off
debug_print_rewritten |=20 off
default_statistics_target | 10
default_transaction_isolatio= n |=20 read committed
dynamic_library_path | $libdir
effective_cache_s= ize |=20 1000
enable_hashjoin | on
enable_indexscan | on
enable_merge= join=20 | on
enable_nestloop | on
enable_seqscan | off
enable_sort |= =20 on
enable_tidscan | on
explain_pretty_print | on
fixbtree |= =20 on
fsync | on
geqo | on
geqo_effort | 1
geqo_generations = |=20 0
geqo_pool_size | 0
geqo_random_seed | -1
geqo_selection_bi= as |=20 2
geqo_threshold | 11
hostname_lookup | off
krb_server_keyfi= le |=20 FILE:/etc/sysconfig/pgsql/krb5.keytab
lc_messages |=20 pt_BR
lc_monetary | pt_BR
lc_numeric | pt_BR
lc_time |=20 pt_BR
log_connections | off
log_duration |=20 off
log_min_error_statement | panic
log_pid | off
log_statem= ent |=20 off
log_timestamp | off
max_connections | 32
max_expr_depth = |=20 10000
max_files_per_process | 1000
max_fsm_pages |=20 10000
max_fsm_relations | 1000
max_locks_per_transaction |=20 64
password_encryption | on
port | 5432
pre_auth_delay |=20 0
random_page_cost | 4
search_path | $user,public
server_enc= oding=20 | SQL_ASCII
server_min_messages | notice
shared_buffers |=20 64
show_executor_stats | off
show_parser_stats |=20 off
show_planner_stats | off
show_source_port |=20 off
show_statement_stats | off
silent_mode | off
sort_mem |= =20 1024
sql_inheritance | on
ssl | off
statement_timeout |=20 0
stats_block_level | off
stats_command_string |=20 off
stats_reset_on_server_start | on
stats_row_level |=20 off
stats_start_collector | on
superuser_reserved_connections |= =20 2
syslog | 0
syslog_facility | LOCAL0
syslog_ident |=20 postgres
tcpip_socket | on
TimeZone | unknown
trace_notify |= =20 off
TRANSACTION ISOLATION LEVEL | READ=20 COMMITTED
transform_null_equals | off
unix_socket_directory |= =20 unset
unix_socket_group | unset
unix_socket_permissions |=20 511
vacuum_mem | 8192
virtual_host | unset
wal_buffers |=20 8
wal_debug | 0
wal_sync_method | fdatasync
zero_damaged_pag= es |=20 off


.
____________________________________________________
  IncrediMail - Email has finally evolved= -=20
Click=20 Here
--------------Boundary-00=_V7JU7TH0000000000000-- --------------Boundary-00=_V7JUCJD0000000000000 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-ID: <7B082358-6D7D-4C3C-9D99-828348CA2EC9> R0lGODlhFAAPALMIAP9gAM9gAM8vAM9gL/+QL5AvAGAvAP9gL////wAAAAAA AAAAAAAAAAAAAAAAAAAAACH/C05FVFNDQVBFMi4wAwEAAAAh+QQJFAAIACwA AAAAFAAPAAAEVRDJSaudJuudrxlEKI6BURlCUYyjKpgYAKSgOBSCDEuGDKgr AtC3Q/R+hkPJEDgYCjpKr5A8WK9OaPFZwHoPqm3366VKyeRtE30tVVRscMHD qV/u+AgAIfkEBWQACAAsAAAAABQADwAABBIQyUmrvTjrzbv/YCiOZGmeaAQA IfkECRQACAAsAgABABAADQAABEoQIUOrpXIOwrsPxiQUheeRAgUA49YNhbCq K1kS9grQhXGAhsDBUJgZAL2Dcqkk7ogFpvRAokSn0p4PO6UIuUsQggSmFjKX dAgRAQAh+QQFCgAIACwAAAAAFAAPAAAEEhDJSau9OOvNu/9gKI5kaZ5oBAAh +QQJFAAIACwCAAEAEAANAAAEShAhQ6ulcg7Cuw/GJBSF55ECBQDj1g2FsKor WRL2CtCFcYCGwMFQmBkAvYNyqSTuiAWm9ECiRKfSng87pQi5SxCCBKYWMpd0 CBEBACH5BAVkAAgALAAAAAAUAA8AAAQSEMlJq7046827/2AojmRpnmgEADs= --------------Boundary-00=_V7JUCJD0000000000000--