Re: [GENERAL] explain, planner and more..
Svenne Krap <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] 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) # 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 [EMAIL PROTECTED] - [EMAIL PROTECTED] - 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
