Re: MACADDR types NULL value (undocumented?)

Judging by the row estimate drop from 1793 to 179 between explains, it
looks as if '00:00:00:00:00:00' is the most common mac value in the host
table.  The second row count is roughly 1/10, or the default selectivity
that postgres uses -- I belive -- for a value that is not the most common
value (guesses row count by taking row count for most common val and
multiplying by 1/10 -- which can be changed).  In this situation, postgres
beilieves that the '00:00:00:00:00:00' value occurs in your database
enough to warrant a sequential scan over the table, instead of an index
scan (which, when doing a large number of tuples, can be slower than a
seq scan due to disk thrashing to get to the correct tuple location).

If the query is faster when run as an index scan, just preceed the query
with:

SET ENABLE_SEQSCAN = 'OFF';
explain select * from host where mac = '00:00:00:00:00:00';

(You can also run these same two commands and compare the total cost from
the explains, which would show that postgres belives the seq scan is
cheaper)

Here is more info on explain:
http://www.postgresql.org/users-lounge/docs/7.0/user/c4884.htm

And, to answer your other question, Postgres would not intrepret
'00:00:00:00:00:00' as null.

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Wed, 21 Mar 2001, Mayers, Philip J wrote:

> hdb=> explain select * from host where mac = '00:00:00:00:00:00';
>
> Seq Scan on host  (cost=0.00..460.62 rows=1793 width=62)
>
> hdb=> explain select * from host where mac = '00:00:00:00:00:01';
>
> Index Scan using host_mac on host  (cost=0.00..145.07 rows=179 width=62)
>
>
>
> I take it that:
>
> 1) 00:00:00:00:00:00 is interpreted as NULL for MACADDR types (grr...)
> 2) NULL tests aren't optimised by indices (I know this already)
>
> This should be added to the documentation. I'm going to have to use a
> different value for my NULL, since I will need to be indexing those...
>
> Regards,
> Phil
>
> +----------------------------------+
> | Phil Mayers, Network Support     |
> | Centre for Computing Services    |
> | Imperial College                 |
> +----------------------------------+
>