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.