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."
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
