Re: [HACKERS] Partial index on date column

2003-03-07 Thread Hannu Krosing 
Christopher Kings-Lynne kirjutas R, 07.03.2003 kell 07:28:
 Yeah, it's not really a problem for me, I just put the extra clause in.
 
 Is indexing excluding NULLs a common application of partial indexes? 

For me it is ;)

 It's
 basically all I use it for, when a column has like 90-95% NULLS and I want
 to exclude them from the index.  

 Is it worth hard-coding in the IS NOT NULL case?

I'd vote for it.


Hannu

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

http://archives.postgresql.org

Re: [HACKERS] Partial index on date column

2003-03-06 Thread Christopher Kings-Lynne 
 The optimizer does not think that pbx_date = CURRENT_DATE satisfies the
 partial index's WHERE condition.  I don't see any really good way around
 this; to improve matters there'd need to be some concept of a plan that
 is only good for a limited time.

It's the same as the slight issue I had:

CREATE INDEX users_users_referrer_idx  ON users_users(referrer) WHERE
(referrer IS NOT NULL);

usa=# explain analyze select * from users_users where referrer=1;
  QUERY PLAN

---
 Seq Scan on users_users  (cost=0.00..3.89 rows=8 width=235) (actual
time=10.51..13.47 rows=8 loops=1)
   Filter: (referrer = 1)

usa=# explain analyze select * from users_users where referrer=1 and
referrer is not null;
   QUERY PLAN


 Index Scan using users_users_referrer_idx on users_users  (cost=0.00..3.01
rows=1 width=235) (actual time=17.12..17.36 rows=8 loops=1)

Obviously to you and I, referrer=1 implies that referrer is not null, but
the planner doesn't know that.  You often have to add a redundant clause to
the query to ensure that the partial index is used.

Chris


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])