Re: how to make this database / query faster

Hi Richard-

My understanding is that Partial index is implemented for low cardinality scenarios ('Y'/'N') ('T'/'F') (null/not null) ?
http://en.wikipedia.org/wiki/Partial_index

Would it matter the selectivity is balanced?
thus 1 null record and 1 trillion null records would not apply

?
Martin-
----- Original Message -----
From: Richard Broersma
To: brian
Cc: pgsql-general(at)postgresql(dot)org
Sent: Sunday, March 16, 2008 10:25 AM
Subject: Re: [GENERAL] how to make this database / query faster

On Sat, Mar 15, 2008 at 5:04 PM, brian <brian(at)zijn-digital(dot)com> wrote:

As there's an index on id would it be faster to transpose the WHERE
conditions?

WHERE id > your_last_id
AND session_key IS NOT NULL

I can't remember if the order of WHERE is significant.

I don't think that the order of WHERE clause conditions are significant with PostgreSQL since it has a sophisticated planner. One way to be sure would be to examine the EXPLAIN plans for different where clause ordering to see if the query plan changes depending upon the order specified.

Another way to make this query faster would be to create a partial index on id;

CREATE UNIQUE INDEX Partial_id
ON Users
WHERE session_key IS NOT NULL;

This index would be much smaller if there are many session_keys that are null, so it should be loaded as searched much faster.