Richard van den Berg wrote:
We have a table with 1M rows that contain sessions with a start and finish timestamps. When joining this table with a 10k table with rounded timestamps, explain shows me sequential scans are used, and the join takes about 6 hours (2s per seq scan on session table * 10000):
Nested Loop (cost=252.80..233025873.16 rows=1035480320 width=97) Join Filter: (("outer".starttime <= "inner".ts) AND ("outer".finishtime
= "inner".ts))
-> Seq Scan on sessions us (cost=0.00..42548.36 rows=924536 width=105) -> Materialize (cost=252.80..353.60 rows=10080 width=8) -> Seq Scan on duration du (cost=0.00..252.80 rows=10080 width=8)
However, during the initial loading of the data (we first load into text tables, then convert to tables using timestamps etc, then run this query) the same query took only 12 minutes. While debugging, I increased cpu_tuple_cost to 0.1 (from 0.01). Now the explain shows an index scan, and the run time comes down to 11 minutes:
Nested Loop (cost=0.00..667700310.42 rows=1035480320 width=97) -> Seq Scan on sessions us (cost=0.00..125756.60 rows=924536 width=105) -> Index Scan using ix_du_ts on duration du (cost=0.00..604.46 rows=1120 width=8) Index Cond: (("outer".starttime <= du.ts) AND ("outer".finishtime >= du.ts))
I am glad that I found a way to force the use of the index, but still can't explain why in the initial run the planner made the right choice, but now I need to give it a hand. Could this have to do with the statistics of the tables? I make very sure (during the initial load and while testing) that I vacuum analyze all tables after I fill them.
I'm runing postgres 7.4.7.
Any help is appreciated.
I believe the problem is that postgres doesn't recognize how restrictive a date-range is unless it uses constants. So saying:
select blah from du WHERE time between '2004-10-10' and '2004-10-15'; Will properly use the index, because it realizes it only returns a few rows. However select blah from du, us where du.ts between us.starttime and us.finishtime; Doesn't know how selective that BETWEEN is.
This has been discussed as a future improvement to the planner (in 8.*). I don't know the current status.
Also, in the future, you really should post your table schema, and explain analyze instead of just explain. (I realize that with a 6hr query it is a little painful.)
Notice that in the above plans, the expected number of rows drops from 10k down to 1k (which is probably where the planner decides to switch). And if you actually did the analyze probably the number of rows is much lower still.
Probably you should try to find out the status of multi-table selectivity. It was discussed in the last couple of months.
John =:->
signature.asc
Description: OpenPGP digital signature
