Thread: Could somebody EXPLAIN? :-)

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

fabrizio.ermini@sysdat.it writes:
> All this figures confuse me. Which one should i use for
> comparison?

The reading of your stopwatch ;-)

The first line's "cost" value is the system's attempt to estimate total
runtime, but it would be foolish to take this as gospel.  Especially so
with a pre-7.0 release, as I see you are using.  There's a number of
problems with cost estimation in old releases, the biggest one for this
example being that SubPlan costs don't get factored into the outer
plan's cost.

            regards, tom lane