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
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 |