X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 12CDDDA9FF for ; Tue, 25 Oct 2005 05:47:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44602-02 for ; Tue, 25 Oct 2005 08:47:54 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.206]) by svr1.postgresql.org (Postfix) with ESMTP id CF446DA9F0 for ; Tue, 25 Oct 2005 05:47:55 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id x7so208267nzc for ; Tue, 25 Oct 2005 01:47:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=JNZ3TY7anDsYIheNKtyO0Ip0k0xPnYuLNg9YSwQRGSU7v810FnUpYQ/2FIwXaHPHjRGBdlUlkoUCXay/dW0Sj7JbhpmNqjFGyxNNeG1LBv+gmsrbFjVT0fG7fOaIy0PWbRaemERR2M1ubOKoyXEiI4X5wCBRPVFj9tCZoU8ewDg= Received: by 10.36.65.7 with SMTP id n7mr4613937nza; Tue, 25 Oct 2005 01:47:57 -0700 (PDT) Received: by 10.36.61.1 with HTTP; Tue, 25 Oct 2005 01:47:57 -0700 (PDT) Message-ID: <42567e060510250147j582ce648p3ceef8e4376975be@mail.gmail.com> Date: Tue, 25 Oct 2005 14:17:57 +0530 From: Kishore B To: pgsql-performance@postgresql.org Subject: Why Index is not working on date columns. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3017_160846.1130230077720" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.308 required=5 tests=[AWL=0.227, HTML_30_40=0.056, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/352 X-Sequence-Number: 15120 ------=_Part_3017_160846.1130230077720 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi All, Thank you very much for your help in configuring the database. Can you guys please take a look at the following query and let me know why the index is not considered in the plan? Here is the extract of the condition string of the query that is taking th= e transaction_date in index condition: *where (account.id =3D search_engine.account_fk) and ( account.status =3D 't' and account.id =3D '40288a820726362f0107263c55d00003') and ( search_engine.id =3D conversion.search_engine_fk and conversion.event_type =3D'daily_spend' and conversion.tactic =3D 'PPC' and conversion.transaction_date between '2005-01-01 00:00:00' and '2005-10-31 23:59:59') group by account.id ; * Plan: *" -> Index Scan using conversion_all on "conversion" (cost=3D0.00..6.02rows=3D1 width=3D98)" " Index Cond: (((tactic)::text =3D 'PPC'::text) AND ((event_type)::text =3D 'daily_spend'::text) AND (transaction_date >=3D '2005-01-01 00:00:00'::timestamp without time zone) AND (transaction_date <=3D '2005-10= -31 23:59:59'::timestamp without time zon (..)" * Here is the extract of the condition string of the query that is not takin= g the transaction_date in index condition: ** where ( account.status =3D 't' and account.id =3D search_engine.account_fk and account.id =3D '40288a820726362f0107263c55d00003' ) and ( search_engine.id =3D conversion.search_engine_fk and conversion.tactic =3D 'PPC' and conversion.transaction_date >=3D '2005-01-01 00:00:00' and conversion.transaction_date <=3D '2005-10-31 23:59:59' ) group by account.id ; *Plan:* *" -> Index Scan using conv_evnt_tact_trans_date_sefk on "conversion" (cost= =3D 0.00..6.02 rows=3D1 width=3D132)" " Index Cond: ((("outer".id)::text =3D ("conversion".search_engine_fk)::tex= t) AND (("conversion".tactic)::text =3D 'PPC'::text))" " Filter: ((transaction_date >=3D '2005-01-01 00:00:00'::timestamp without time zone) AND (transaction_date <=3D '2005-10-31 23:59:59'::timestamp with= out time zone))" * ** I have the following indexes defined on the columns. *conv_evnt_tact_trans_date_sefk : (search_engine_fk, tactic, event_type, transaction_date);* *conv_all : (tactic, event_type, transaction_date);* ** I am really confused when I saw this plan. In both queries, I am using the same columns in the where condition, but the optimizer is taking different indexes in these two cases. Second, even though, I have the transaction_date column specified in the second instance, why is it not taking the constraint as index condition? Thanks in advance. Thank you, Kishore. ------=_Part_3017_160846.1130230077720 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi All,
 
Thank you very much for your help in configuring the database.
 
Can you guys please take a look at the following query and let me=  know why the index is not considered in the plan?
 
Here is the extract of the condition string of the query that is takin= g the transaction_date in index condition:
 
where (account.id =3D search_eng= ine.account_fk) and ( account.status =3D 't' and account.id =3D '40288a820726362f0107263c55d00003') and (  sear= ch_engine.id =3D=20 conversion.search_engine_fk   and conversion.event_type =3D'daily= _spend' and conversion.tactic =3D 'PPC' and conversion.transaction_date bet= ween '2005-01-01 00:00:00' and '2005-10-31 23:59:59') group by account.id;
Plan:
"        ->  Index= Scan using conversion_all on "conversion"  (cost=3D0.00..6.= 02 rows=3D1 width=3D98)"
"      =         Index Cond: (((tactic)::text =3D= 'PPC'::text) AND ((event_type)::text =3D 'daily_spend'::text) AND (transac= tion_date >=3D '2005-01-01 00:00:00'::timestamp without time zone) AND (= transaction_date <=3D '2005-10-31 23:59:59'::timestamp without time zon = (..)"
Here is the extract of the condition string of the query that is not &= nbsp;taking the transaction_date in index condition:
 
where ( account.status =3D 't' and accou= nt.id  =3D search_engine.account_fk and account.id =3D '40288a820726362f0107263c55d00003' ) and (  sea= rch_engine.id =3D=20 conversion.search_engine_fk   and conversion.tactic =3D 'PPC' and= conversion.transaction_date >=3D '2005-01-01 00:00:00' and conversion.t= ransaction_date <=3D '2005-10-31 23:59:59'  ) group by account.id ;
 
Plan:
"        ->  Index= Scan using conv_evnt_tact_trans_date_sefk on "conversion"  = (cost=3D0.00..6.02 rows=3D1 width=3D132)"
"   &= nbsp;          Index Cond: (((= "outer".id)::text =3D ("conversion".search_engine_fk)::= text) AND (("conversion".tactic)::text =3D 'PPC'::text))"
"           = ;   Filter: ((transaction_date >=3D '2005-01-01 00:00:00'::tim= estamp without time zone) AND (transaction_date <=3D '2005-10-31 23:59:5= 9'::timestamp without time zone))"
 
I have the following indexes defined on the columns.
conv_evnt_tact_trans_date_sefk : (search_engine_fk, tacti= c, event_type, transaction_date);
conv_all : (tactic, event_type, transaction_date);
 
I am really confused when I saw this plan. In both queries, I am using= the same columns in the where condition, but the optimizer is taking diffe= rent indexes in these two cases.
Second, even though, I have the transaction_date column specified in t= he second instance, why is it not taking the constraint as index condition?=
 
Thanks in advance.
 
Thank you,
Kishore.
------=_Part_3017_160846.1130230077720--