explain math question

From: Joseph Shraibman <joseph(at)xtenit(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: explain math question
Date: 2002-11-01 00:57:58
Message-ID: 3DC1D196.3050909@xtenit.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I changed a query that used a subselect to user a more efficient subselect. Here are the
explains for the old way and the new way:

OLD WAY:
Unique (cost=47980.80..49226.66 rows=1780 width=789)
-> Sort (cost=47980.80..47980.80 rows=17798 width=789)
-> Hash Join (cost=18737.75..32994.32 rows=17798 width=789)
-> Seq Scan on u (cost=0.00..7211.12 rows=17798 width=627)
-> Hash (cost=7432.70..7432.70 rows=162170 width=162)
-> Seq Scan on d (cost=0.00..7432.70 rows=162170 width=162)
SubPlan
-> Nested Loop (cost=0.00..11.19 rows=1 width=82)
-> Index Scan using a_pkey on a (cost=0.00..5.86 rows=1 width=4)
-> Index Scan using pu_pkey on pu (cost=0.00..5.31 rows=1 width=78)
-> Limit (cost=17.51..17.51 rows=1 width=10)
-> Sort (cost=17.51..17.51 rows=4 width=10)
-> Index Scan using ml_u_and_p_key on ml (cost=0.00..17.46
rows=4 width=10)

NEW WAY:

Unique (cost=48031.59..49277.45 rows=1780 width=793)
-> Sort (cost=48031.59..48031.59 rows=17798 width=793)
-> Hash Join (cost=18680.53..32955.10 rows=17798 width=793)
-> Seq Scan on u (cost=0.00..7211.12 rows=17798 width=631)
-> Hash (cost=7432.70..7432.70 rows=162170 width=162)
-> Seq Scan on d (cost=0.00..7432.70 rows=162170 width=162)
SubPlan
-> Nested Loop (cost=0.00..11.19 rows=1 width=82)
-> Index Scan using a_pkey on a (cost=0.00..5.86 rows=1 width=4)
-> Index Scan using pu_pkey on pu (cost=0.00..5.31 rows=1 width=78)
-> Index Scan using ml_u_and_p_key on ml (cost=0.00..3.02 rows=1 width=2)

Note that the cost of the scan using ml_u_and_p_key went from 17.46 to 3.02, but the total
cost of the query went up. What's going on?

Also does explain add the cost of the subselect for each time it is executed? It seems to
add the cost as if it is executed only once. In the first I did (SELECT field FROM ml
where u.u = ml.u order by field2 desc limit 1). In the second I added a field to u that
refrences the record in ml, and the query looks like (SELECT field FROM ml where u.ref =
ml.id)

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2002-11-01 01:54:12 Re: [GENERAL] my.cnf to postgresql.conf Conversion
Previous Message Paul Ottar Tornes 2002-10-31 23:39:22 limit