Re: reading EXPLAIN output

David Rysdam <drysdam@ll.mit.edu> writes:
> merge join (cost=0.00..348650.65 rows=901849 width=12)
>   merge cond {blah}
>   join filter {blah}
>      index scan using {blah index on blah} (cost=0.00..289740.65
> rows=11259514 width=8)
>      index scan using {blah index on blah} (cost=0.00..17229.93
> rows=902085 width=8)

> This query takes about 3 minutes to run and I'm trying to figure out
> why.  From a tutorial and the docs, I gather that the "..largenum" part
> is the number of page reads required, so I understand where 289740 and
> 17229 come from.  But what about 348650 page reads for the "merge
> join"?

You're misreading it.  An upper node's cost includes the cost of its
children.  So the actual cost estimate for the join step is 41680.07.

> When I do EXPLAIN ANALYZE, the actual values come out like this:

> merge join: (actual time=170029.404..170029.404)

That seems a bit odd ... is there only one row produced?  Could you show
us the entire EXPLAIN ANALYZE output, rather than your assumptions about
what's important?

Increasing work_mem won't help a merge join, but if you can get it large
enough to allow a hash join to be used instead, that might be a win.

            regards, tom lane