Could somebody EXPLAIN? :-)

From: fabrizio(dot)ermini(at)sysdat(dot)it
To: pgsql-general(at)postgresql(dot)org
Subject: Could somebody EXPLAIN? :-)
Date: 2000-12-21 15:07:43
Message-ID: 3A422ACF.13194.BD42690@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.
I wanted to compare the performances of 2 ways of writing a query,
one using a cartesian join, one using a subselect, to see which
one was the faster.
I used the EXPLAIN command to understand how Postgres
planned to execute them, but the results are a little obscure.
Can somebody shed some light?

Here are the results of the explains:

With the join:

EXPLAIN
SELECT distinct s.* FROM items_products AS r, support AS s
WHERE r.family_name='XXX'
AND r.item_id=s.id
ORDER BY s.date DESC

NOTICE: QUERY PLAN:

Sort (cost=38.89 rows=2 width=116)
-> Nested Loop (cost=38.89 rows=2 width=116)
-> Seq Scan on items_products r (cost=36.84 rows=1
width=4)
-> Index Scan using support_id_key on support s
(cost=2.05 rows=382
width=112)

With the subselect:

EXPLAIN
SELECT * FROM support WHERE id IN (SELECT
DISTINCT(item_id) FROM
items_products WHERE family_name='XXX') ORDER BY date
DESC;

NOTICE: QUERY PLAN:

Sort (cost=23.61 rows=382 width=112)
-> Seq Scan on support (cost=23.61 rows=382 width=112)
SubPlan
-> Unique (cost=36.84 rows=1 width=4)
-> Sort (cost=36.84 rows=1 width=4)
-> Seq Scan on items_products (cost=36.84
rows=1 width
=4)

----

(I could also post table structure, if it's of any help).

All this figures confuse me. Which one should i use for
comparison?

TIA, merry Xmas to all!

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini Alternate E-mail:
C.so Umberto, 7 faermini(at)tin(dot)it
loc. Meleto Valdarno Mail on GSM: (keep it short!)
52020 Cavriglia (AR) faermini(at)sms(dot)tin(dot)it

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dale Anderson 2000-12-21 15:26:51 7.1 Beta 1 & table size calculation
Previous Message Jeff Eckermann 2000-12-21 14:51:39 RE: Off topic? - Solution for a delimiter problem