Re: Query planner question - Mailing list pgsql-general

At 02:12 13.06.2003, Dmitry Tkach said:
--------------------[snip]--------------------
>>Nope - it's still using id_owid... but anyway that's the least problem as
>>it uses an index anyway (I can live with the 12 msec)
>>
>>
>Weird ....
>Can you send the analyze output?

explain analyze select distinct on (owid,dcid,dsid,drid) dcid,dsid,drid
from table where owid = 2;
NOTICE:  QUERY PLAN:

Unique  (cost=385.76..386.83 rows=11 width=24) (actual time=11.61..12.98
rows=512 loops=1)
  ->  Sort  (cost=385.76..385.76 rows=106 width=24) (actual
time=11.60..11.93 rows=512 loops=1)
        ->  Index Scan using id_owid on table  (cost=0.00..382.18 rows=106
width=24) (actual time=0.05..3.43 rows=512 loops=1)
Total runtime: 14.00 msec

EXPLAIN


>>>create id_dictid_noid on table (dcid, dsid, drid, nxid) where owid is null;
>>
>>Tried this, to no avail - still using sequential scan...
>>
>Even more weird!
>Analyze?

create index id_dictid_noid on table (dcid,dsid,drid) where owid is null;
CREATE
explain select distinct dcid,dsid,drid from table where owid is null;
NOTICE:  QUERY PLAN:

Unique  (cost=128002.92..129507.00 rows=20054 width=20)
  ->  Sort  (cost=128002.92..128002.92 rows=200543 width=20)
        ->  Seq Scan on table  (cost=0.00..106951.52 rows=200543 width=20)

EXPLAIN

>May be a good idea, regardless - personally, I don't like this partial
>indexes stuff, because, first, it is not standard, second, it wastes
>space, and third it just makes life too damn complicated...

I'm using them (in the same application, and the same table) with good
success. This table has a number of columns where only one is used of the
whole set (this is used for an object oriented DB kernel where scalar data
types map to their DB type counterparts). A control bitfield points out
which columns are to be considered for a particular row, and it helps to
keep the index activity at a necessary minimum.

>If it doesn't cause any problems (like, with 0 being a valid regular
>value, or with foreign key/uniqueness  constraints, or outside code that
>relies on nulls), you are better off using 0 (or -1 or whatever) instead
>of null - and add a 'not null' on the column definition while you are at
>it, so that you don't get those zeroes and null mixed together :-)

Actually I can't use any number than null since owid (owner ID) has a
foreign key pointing to the owning row, or is null if there's none. So I
simply set up a "helper row" that "owns" those rows that are in fact
unowned - the row ID being a reserved value (I'm using -1 as you
suggested). Only the application has to know when querying and writing
rows, and that's a single place. ACK and done ;-)

>Nobody seems to like nulls for some reason. I was always wonderring - WHY???
>Ancient people did not know 0, and then, after they finally invented it,
>it took centuries more to get them to believe, that it was actually a
>number...
>The modern database engines seem to be handling nulls the same way :-)

I always recognized "null" as a very important value (non-value, better
then). I believe it should be indexable.

>So, it looks like the plan it chose on its own was better, right?

Absolutely,

--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/