Re: Weird index or sort behaviour

On Tue, 18 Aug 2009, Tom Lane wrote:
>> I would be more curious in the poster's situation to turn off
>> enable_seqscan, enable_sort, and/or enable_nestloop see how the index
>> scan merge join plan runs.

Like this:

                             QUERY PLAN
-----------------------------------------------------------------------
  Aggregate
    (cost=2441719.92..2441719.93 rows=1 width=0)
    (actual time=50087.537..50087.538 rows=1 loops=1)
    ->  HashAggregate
         (cost=2397366.95..2417079.38 rows=1971243 width=28)
         (actual time=40462.069..48634.713 rows=17564726 loops=1)
          ->  Merge Join
                (cost=0.00..2362870.20 rows=1971243 width=28)
                (actual time=0.095..22041.693 rows=21463106 loops=1)
                Merge Cond: ((l1.objectid = l2.objectid) AND (l1.bin = l2.bin))
                Join Filter: ((l1.intermine_start <= l2.intermine_end) AND (l2.intermine_start <= l1.intermine_end))
                ->  Index Scan using locationbin8000__subjectobjectbin on locationbin8000 l1
                      (cost=0.00..71635.23 rows=657430 width=20)
                      (actual time=0.056..170.857 rows=664588 loops=1)
                      Index Cond: (subjecttype = 'GeneFlankingRegion'::text)
                ->  Index Scan using locationbin8000__subjectobjectbin on locationbin8000 l2
                      (cost=0.00..71635.23 rows=657430 width=20)
                      (actual time=0.020..9594.466 rows=38231659 loops=1)
                      Index Cond: (l2.subjecttype = 'GeneFlankingRegion'::text)
  Total runtime: 50864.569 ms
(10 rows)

>> rewinding an index scan is more expensive than rewinding a materialize
>> node but would it really be so much expensive that it's worth copying
>> the entire table into temporary space?
>
> Absolutely not, but remember that what we're expecting the Materialize
> to do is buffer only as far back as the last Mark, so that it's unlikely
> ever to spill to disk.

If that's how it works, then that sounds very promising indeed.

> In particular, in Matthew's example the sort is being estimated at
> significantly higher cost than the indexscan, which presumably means
> that we are estimating there will be a *lot* of re-fetches, else we
> wouldn't have rejected the indexscan on the inside.

select sum(c * c) / sum(c) from (select objectid, bin, count(*) AS c from
locationbin8000 where subjecttype = 'GeneFlankingRegion' GROUP BY
objectid, bin) as a;
       ?column?
---------------------
  57.5270393085641029

So on average, we will be rewinding by 57 rows each time. A materialise
step really does sound like a win in this situation.

Matthew

--
 Patron: "I am looking for a globe of the earth."
 Librarian: "We have a table-top model over here."
 Patron: "No, that's not good enough. Don't you have a life-size?"
 Librarian: (pause) "Yes, but it's in use right now."