By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,821 Members | 1,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,821 IT Pros & Developers. It's quick & easy.

Analyze not doing anything?

P: n/a
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 ji*@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?"

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

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
"Jim C. Nasby" <ji*@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

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

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #2

P: n/a
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" <ji*@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 ji*@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?"

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

Nov 22 '05 #3

P: n/a
On Mon, Feb 09, 2004 at 18:39:48 -0600,
"Jim C. Nasby" <ji*@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.

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

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.