Re: More question about plans & explain (long) - Mailing list pgsql-admin

"Nick Fankhauser" <nickf@ontko.com> writes:
> 14)                    ->  Hash Join  (cost=1.12..17251.58 rows=18219
> width=72)
> 15)                          ->  Seq Scan on case_data  (cost=0.00..5407.91
> rows=182191 width=48)
> 16)                          ->  Hash  (cost=1.10..1.10 rows=10 width=24)
> 17)                                ->  Seq Scan on court_config
> (cost=0.00..1.10 rows=10 width=24)
> 18)              ->  Sort  (cost=6781.25..6781.25 rows=2352 width=104)
> 19)                    ->  Index Scan using event_event_date_time on event
> (cost=0.00..6649.52 rows=2352 width=104)

> Here are my questions-

> (14-17)It looks like on lines 14-17, case_data and court_config are being
> joined without any constraint, thus returning 182191 rows, but the join &
> sort on 13 & 14 indicate 18219 rows are expected - why does the planner
> expect 1/10th of what I expect? Is there a way to affect this expectation?

A hash join always has some constraint --- otherwise there'd be nothing
to hash on.  In this case it must be using case_data.court_id =
court_config.court_id as the hash clause; I can't see any other
candidate.  It would appear that the system is estimating that the join
selectivity is 0.01 (ie, the estimated number of output rows is only
0.01 of what a full Cartesian product would be).  This seems a
suspiciously round number.  Since it is in fact the default selectivity
estimate (at least pre-7.2) I wonder whether you've ever VACUUM ANALYZEd
these tables.  You seem to have number-of-rows stats but nothing more
for most of these tables.

BTW, current sources display the constraint clauses being used at each
plan step, which takes some of the guesswork out of interpreting EXPLAIN
displays.

> 10)This table has a unique index on the field being scanned, and this field
> is also the only row returned from the table- why is a seq scan chosen here?

It's unlikely that an indexscan would be faster than a seqscan+sort for
obtaining the whole table in sorted order.  Sad but true.

> 15)This table has a unique index on the field being scanned, and only 3 rows
> are returned to the query- why the seq scan?

AFAICT there is no constraint that would allow the three rows to be
identified in advance of doing the join.

> (18-19)Is the row count of 2352 from lines 18 and 19 based on a reasonable
> guess that out of the 235239 rows in the table, we'll select one out of 100?
> Again, where does this number come from, and can I affect it?

See above.

> 12)Line 12 seems to indicate that the planner expects this join to produce
> more rows rather than fewer as in the case of line 14 and line 18.

Still looks like an 0.01 estimate to me ...

> 8)The planner seems to expect 15 rows when the table actually has 66559.
> This is so much different from the 1:100 ratio in 19 that I wonder how it
> was arrived at? (It *is* a very good guess, since the actual number retturn
> will be 1.)

It would seem that you do have ANALYZE stats for actor_identifier.

> What is the difference between a hash join & a merge join?

Hash builds a hashtable of the inner relation and then probes it for
each outer-relation tuple.  Merge wants the two relations to be
pre-sorted on the join key, and then it scans them in parallel.

> Does materialize fetch the other fields given a list of index values?

No, it instantiates the result of the subplan as a temp file (hopefully
all in memory, but may spill to a temp file if it gets too big) so that
the parent plan can rescan it multiple times at relatively low cost.
In this case the reason for the materialize is that the outer mergejoin
may want to back up and rescan portions of its subplan's result, and
the inner mergejoin can't cope.  This is actually a pretty dumb plan;
if we were properly accounting for the cost of the materialize then this
wouldn't have been chosen.  (Simply reversing the order of the outer
and inner plans of the top merge would have eliminated the need for the
materialize.)

> Does a seq scan read the entire table, or can it just retrieve the field?

Reading a tuple is reading a tuple; there's not really any difference.

            regards, tom lane