Re: Spatial join insists on sequential scan of larger

On Fri, 2 Apr 2004, Tom Lane wrote:

> Could we see EXPLAIN ANALYZE output?

The original EXPLAIN output was:

QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=0.00..196642756520.34 rows=49506496044 width=32)
-> Seq Scan on twomass t (cost=0.00..9560002.72 rows=177023872 width=48)
-> Index Scan using xmm1box on xmm1 x (cost=0.00..1107.28 rows=280 width=48)
Index Cond: (x.errbox && "outer".errbox)

The EXPLAIN ANALYZE query was:

explain analyze
SELECT x.ra AS xra, x.decl AS xdecl, t.ra AS tra, t.decl AS tdecl
INTO tempjoin
FROM xmm1 AS x INNER JOIN twomass AS t
ON x.errbox && t.errbox;

And this produced:

\timing
Timing is on.
dw=# \i join1.sql
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..196642756520.34 rows=49506496044 width=32) (actual time=701.919..7796111.624 rows=1513 loops=1)
-> Seq Scan on twomass t (cost=0.00..9560002.72 rows=177023872 width=48) (actual time=22.064..617462.486 rows=177757299 loops=1)
-> Index Scan using xmmbox on xmm1 x (cost=0.00..1107.28 rows=280 width=48) (actual time=0.036..0.036 rows=0 loops=177757299)
Index Cond: (x.errbox && "outer".errbox)
Total runtime: 7796410.533 ms
(5 rows)

Time: 7796996.093 ms

--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH, U.K.