DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=K6UNfQxYNBxFi8TPACcestMREtkbaGV48vIyjWg3aoDNH2ziMrALTfoz549Zvh2i0vkuqTOkOPRX6Gs62Gj8BB+A8dg2RGr74Nx9hHJ2Smr8/HDa2M/g03+YCuqvJ84e3TItWWTCFoU74yrfROFLMD5i6NphmsSLL8AORgoLr3U=
Message-ID: <b048c51b0708220557g18cc9a6p7bf2ab195539c3bd@mail.gmail.com>
Date: Wed, 22 Aug 2007 16:57:42 +0400
From: "Aleksandr Vinokurov" <aleksandr.vin@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Partial index and query plan
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello all,

Imagine having this table:

create table user_history (
   rec_id               SERIAL not null,
   date                 TIMESTAMP            not null,
   action               INT2                 not null,
   uid                  INT4                 not null,
   name                 CHAR(10)             null default NULL,
   constraint PK_USER_HISTORY primary key (rec_id),
   constraint AK_DATE_USER_HIS unique (date)
);

and this partial index:

create unique index indx_date_action12_uid_user_his
on user_history (date, uid)
where action <> 0;

and this query:

select date
from "user_history"
where date > '2007-08-18 14:33'
and date <= '2007-08-18 16:30'
and uid = 1388
and action <> 0
limit 1;


The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in plan:

Limit  (cost=0.00..3.05 rows=1 width=8) (actual time=4.798..4.798
rows=0 loops=1)
   ->  Index Scan using indx_date_action12_uid_user_his on
user_history  (cost=0.00..6.10 rows=2 width=8) (actual
time=4.791..4.791 rows=0 loops=1)
         Index Cond: ((date > '2007-08-18 14:33:40.60664'::timestamp
without time zone) AND (date <= '2007-08-18 16:30:00'::timestamp
without time zone) AND (uid = 138658))
         Filter: ("action" <> 0)

when this is a "where" case of the index?