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?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Aleksandr Vinokurov" <aleksandr.vin@gmail.com> writes:
> The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in plan:

Use a newer Postgres release (8.1 or later).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thank you Tom,

but does it means that this is only an explain's problem or the plan
is actually such a hard, and postmaster actually checks each record
found by the index with this "filter"?

I'm using 8.0.1 version, but upgrading can become a work with expense.

On 22/08/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Aleksandr Vinokurov" <aleksandr.vin@gmail.com> writes:
> > The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in plan:
>
> Use a newer Postgres release (8.1 or later).
>
> regards, tom lane
>

With best wishes, Aleksandr.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 8/22/07, Aleksandr Vinokurov <aleksandr.vin@gmail.com> wrote:
>
> 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)
> );
>
> 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)

I don't see the issue here. The index being used is the same partial
index you created. Maybe it's just a question of semantics?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Scott Marlowe wrote:
>
> I don't see the issue here. The index being used is the same partial
> index you created. Maybe it's just a question of semantics?
>

As I understand final filter is:
a) pointed at the index creation
b) is redundant as all the indexed records have action <> 0.

So checking of it is a time wasting.

And the plan should be this:

>> 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))


I suggest that this 'Filter' check will not be noticed as it always
return TRUE, and so will be checked only once, -- because of the
"limit 1".

But thanks, Aleksandr.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org