RE: MACADDR types NULL value (undocumented?)

"Mayers, Philip J" <p.mayers@ic.ac.uk> writes:

Oops, yes, you're quite correct: There are some 17700 entries in the table,
with 1793 being 00:00:00:00:00:00 - and that warrants a sequential scan,
you're correct. Damn :o)

If these all-zero entries (a) are really dummy values ("don't know" or
"not applicable"), and (b) outnumber any specific real entry, then it
would be worth your while to replace them with NULLs. The statistics
stuff accounts for NULLs separately from not-nulls, so after a vacuum
analyze you'd find the planner more able to make an intelligent choice
about seq vs index scan on this table.

regards, tom lane

I was under the impression that indices aren't used for "IS NULL" - I will
need to be scanning for these moderately frequently (but less frequently
than the others). In actual fact, it would be better (from an architectural
point of view) for me for them to be NULL.

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support |
| Centre for Computing Services |
| Imperial College |
+----------------------------------+

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 26 March 2001 15:45
To: Mayers, Philip J
Cc: 'Michael Fork'; 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] MACADDR types NULL value (undocumented?)

"Mayers, Philip J" <p.mayers@ic.ac.uk> writes:

Oops, yes, you're quite correct: There are some 17700 entries in the

table,

with 1793 being 00:00:00:00:00:00 - and that warrants a sequential scan,
you're correct. Damn :o)

If these all-zero entries (a) are really dummy values ("don't know" or
"not applicable"), and (b) outnumber any specific real entry, then it
would be worth your while to replace them with NULLs. The statistics
stuff accounts for NULLs separately from not-nulls, so after a vacuum
analyze you'd find the planner more able to make an intelligent choice
about seq vs index scan on this table.

regards, tom lane

"Mayers, Philip J" <p.mayers@ic.ac.uk> writes:

I was under the impression that indices aren't used for "IS NULL"

They're not (at present). But given those numbers you wouldn't want an
indexscan when looking for nulls anyway, so where's the downside?

regards, tom lane

Good point - thanks for the advice

(Tom - where *do* you get the time to answer everyones questions? :o)

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support |
| Centre for Computing Services |
| Imperial College |
+----------------------------------+

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 26 March 2001 16:05
To: Mayers, Philip J
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] MACADDR types NULL value (undocumented?)

"Mayers, Philip J" <p.mayers@ic.ac.uk> writes:

I was under the impression that indices aren't used for "IS NULL"

They're not (at present). But given those numbers you wouldn't want an
indexscan when looking for nulls anyway, so where's the downside?

regards, tom lane