Could somebody EXPLAIN? :-)
От | fabrizio.ermini@sysdat.it |
---|---|
Тема | Could somebody EXPLAIN? :-) |
Дата | |
Msg-id | 3A422ACF.13194.BD42690@localhost обсуждение исходный текст |
Ответы |
Re: Could somebody EXPLAIN? :-)
|
Список | 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@tin.it loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) faermini@sms.tin.it
В списке pgsql-general по дате отправления: