Thread: Analyze not doing anything?

I build a table to test the theory that PGSQL wouldn't use an index to
satisfy 'SELECT * FROM table WHERE field IS NOT NULL'. Sure enough it
wasn't using the index, but it seems that's because there's no stats to
be had. What am I doing wrong? This is version 7.3.4.

stats=# \t
Showing only tuples.
stats=# select * from pg_stats where tablename='t';

stats=# analyze t;
ANALYZE
stats=# select * from pg_stats where tablename='t';

stats=# select count(*) from pg_stats where tablename='email_contrib';
     5
stats=# \t
Tuples only is off.
stats=# select relname, relpages, reltuples from pg_class where relname ='moo' or relname='t';
 relname | relpages | reltuples
---------+----------+-----------
 moo     |      289 |    131073
 t       |    32769 |    131076
(2 rows)

stats=# \d t
            Table "public.t"
 Column |      Type       |  Modifiers
--------+-----------------+-------------
 i      | integer         |
 c      | character(1950) | default 'x'
Indexes: moo btree (i)

stats=#

--
Jim C. Nasby, Database Consultant                  jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <jim@nasby.net> writes:
> I build a table to test the theory that PGSQL wouldn't use an index to
> satisfy 'SELECT * FROM table WHERE field IS NOT NULL'.

IS NULL/IS NOT NULL are not indexable operators.

            regards, tom lane

Hrm, I didn't realize that. Is it in the docs anywhere? I didn't see it
in Chapter 11... I'm particularly interested in why NULL/NOT NULL isn't
indexable.

Are where clauses on indexes like

email_contrib__team_id btree (team_id) WHERE (team_id IS NOT NULL)

still valid/usefull? If I wanted to create the converse of that index,
could I do something like

CREATE INDEX email_contrib__no_team ON
email_contrib(COALESCE(team_id,true)) WHERE team_id IS NULL;

and

SELECT ... WHERE COALESCE(team_id, true) = true;

?

On Thu, Feb 05, 2004 at 03:23:16PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > I build a table to test the theory that PGSQL wouldn't use an index to
> > satisfy 'SELECT * FROM table WHERE field IS NOT NULL'.
>
> IS NULL/IS NOT NULL are not indexable operators.
>
>             regards, tom lane
>

--
Jim C. Nasby, Database Consultant                  jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Mon, Feb 09, 2004 at 18:39:48 -0600,
  "Jim C. Nasby" <jim@nasby.net> wrote:
> Hrm, I didn't realize that. Is it in the docs anywhere? I didn't see it
> in Chapter 11... I'm particularly interested in why NULL/NOT NULL isn't
> indexable.
>
> Are where clauses on indexes like
>
> email_contrib__team_id btree (team_id) WHERE (team_id IS NOT NULL)
>
> still valid/usefull? If I wanted to create the converse of that index,

While IS NULL and IS NOT NULL are not indexable, they can be used as
restrictions for partial indexes. If the matching clause is used in
a where clause, then the partial index could potentially be used
for executing the query.