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=177757299loops=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.