Thread: explain, planner and more..

Hi,

how has the following to be read.. (ie. what is the total cost of the
query)... 4.05, 5.88 or ?

Why does the planner choose not to use numberdomain_pkey as index on
numberdomain ?

The table layout is quite bad (due to a lot of last minute-changes)

<snip>
# explain select c.*, (select count(*) from numberservice ns where
ns.customerid=c.customerid and (exists (select * from numbermail nm
where nm.domainnr = ns.domainnr) or exists(select * from numberdomain
nd where nd.domainnr=ns.domainnr))) as amount from customer c where
c.status=0;
NOTICE:  QUERY PLAN:

Seq Scan on customer c  (cost=0.00..4.05 rows=46 width=200)
  SubPlan
    ->  Aggregate  (cost=5.88..5.88 rows=1 width=0)
          ->  Index Scan using numberservice_customerid_ix on
numberservice ns  (cost=0.00..5.88 rows=1 width=0)
                SubPlan
                  ->  Index Scan using numbermail_pkey on numbermail
nm  (cost=0.00..2.01 rows=1 width=47)
                  ->  Seq Scan on numberdomain nd  (cost=0.00..1.85
rows=1 width=31)

EXPLAIN
--
Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022
ICQ: 5434480 - http://www.krap.dk - http://www.krap.net
PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022

Svenne Krap <usenet@krap.dk> writes:
> Why does the planner choose not to use numberdomain_pkey as index on
> numberdomain ?

>                   ->  Seq Scan on numberdomain nd  (cost=0.00..1.85
> rows=1 width=31)

Evidently because it thinks numberdomain only has one disk block,
and hence there's no possible savings from reading an index in addition
to that one disk block.  (If it were estimating more than one block
read then the cost estimate would be 2 or more.  1.85 implies one block
read = 1.0 cost unit, plus some per-tuple CPU effort.)

If numberdomain is indeed big enough to warrant an index search,
then you need to VACUUM it to update the planner's statistics.
The plan you are getting is based on statistics that say numberdomain
is tiny.

            regards, tom lane