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@tin.it
loc. Meleto Valdarno Mail on GSM: (keep it short!)
52020 Cavriglia (AR) faermini@sms.tin.it
