[postgis-devel] RE: join selectivity
strk at refractions.net
strk at refractions.net
Tue Dec 14 02:22:44 PST 2004
On Tue, Dec 14, 2004 at 10:15:14AM -0000, Mark Cave-Ayland wrote:
> Hi strk,
>
> Could you post the explain outputs for those queries too? I don't see why
> with an index scan consideration between two columns that the planner has to
> invoke four RESTRICT functions?
strk=# explain analyze select * from test1, test2 where test1.geom && test2.geom;
NOTICE: LWGEOM_gist_joinsel called (returning 0.000005)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3.27..105.84 rows=1 width=64) (actual time=0.217..39.305 rows=2700 loops=1)
Join Filter: ("inner".geom && "outer".geom)
-> Seq Scan on test2 (cost=0.00..28.32 rows=132 width=32) (actual time=0.081..1.111 rows=108 loops=1)
-> Materialize (cost=3.27..3.52 rows=25 width=32) (actual time=0.001..0.011 rows=25 loops=108)
-> Seq Scan on test1 (cost=0.00..3.25 rows=25 width=32) (actual time=0.043..0.129 rows=25 loops=1)
Total runtime: 40.471 ms
(6 rows)
strk=# create index test2_gist on test2 using gist (geom gist_geometry_ops); CREATE INDEX
strk=# explain analyze select * from test1, test2 where test1.geom && test2.geom;
NOTICE: LWGEOM_gist_joinsel called (returning 0.000005)
NOTICE: LWGEOM_gist_sel called
NOTICE: no constant arguments - returning default selectivity
NOTICE: LWGEOM_gist_sel called
NOTICE: no constant arguments - returning default selectivity
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3.27..92.11 rows=1 width=64) (actual time=0.046..39.219 rows=2700 loops=1)
Join Filter: ("inner".geom && "outer".geom)
-> Seq Scan on test2 (cost=0.00..28.08 rows=108 width=32) (actual time=0.009..0.198 rows=108 loops=1)
-> Materialize (cost=3.27..3.52 rows=25 width=32) (actual time=0.000..0.013 rows=25 loops=108)
-> Seq Scan on test1 (cost=0.00..3.25 rows=25 width=32) (actual time=0.002..0.052 rows=25 loops=1)
Total runtime: 40.307 ms
(6 rows)
strk=# create index test1_gist on test1 using gist (geom gist_geometry_ops); CREATE INDEX
strk=# explain analyze select * from test1, test2 where test1.geom && test2.geom;
NOTICE: LWGEOM_gist_joinsel called (returning 0.000005)
NOTICE: LWGEOM_gist_sel called
NOTICE: no constant arguments - returning default selectivity
NOTICE: LWGEOM_gist_sel called
NOTICE: no constant arguments - returning default selectivity
NOTICE: LWGEOM_gist_sel called
NOTICE: no constant arguments - returning default selectivity
NOTICE: LWGEOM_gist_sel called
NOTICE: no constant arguments - returning default selectivity
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3.27..92.11 rows=1 width=64) (actual time=0.052..38.867 rows=2700 loops=1)
Join Filter: ("inner".geom && "outer".geom)
-> Seq Scan on test2 (cost=0.00..28.08 rows=108 width=32) (actual time=0.012..0.181 rows=108 loops=1)
-> Materialize (cost=3.27..3.52 rows=25 width=32) (actual time=0.000..0.010 rows=25 loops=108)
-> Seq Scan on test1 (cost=0.00..3.25 rows=25 width=32) (actual time=0.002..0.032 rows=25 loops=1)
Total runtime: 40.027 ms
(6 rows)
... MM.. my database might be corrupted somehow:
strk=# vacuum full analyze test1 ;
ERROR: index "pg_toast_1181604_index" is not a btree
strk=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.0rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031005 (Debian prerelease)
(1 row)
.. But I'm sure I used initdb !
--strk;
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> South West Technology Centre
> Tamar Science Park
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 791021
> F: +44 (0)1752 791023
> W: http://www.webbased.co.uk
>
>
> > -----Original Message-----
> > From: strk at refractions.net [mailto:strk at refractions.net]
> > Sent: 13 December 2004 17:13
> > To: Mark Cave-Ayland
> > Cc: postgis-devel at postgis.refractions.net;
> > pgsql-hackers at postgresql.org
> > Subject: Re: [postgis-devel] RE: join selectivity
> >
> >
> > On Mon, Dec 13, 2004 at 03:04:01PM -0000, Mark Cave-Ayland wrote:
> > > Hi strk,
> > >
> > > > -----Original Message-----
> > > > From: strk at refractions.net [mailto:strk at refractions.net]
> > > > Sent: 13 December 2004 14:05
> > > > To: Mark Cave-Ayland
> > > > Cc: postgis-devel at postgis.refractions.net
> > > > Subject: Re: [postgis-devel] RE: join selectivity
> > > >
> > > >
> > > > On Mon, Dec 13, 2004 at 12:16:15PM -0000, Mark Cave-Ayland wrote:
> > > > > Hi strk,
> > > > >
> > > > > (cut)
> > > > >
> > > > > > > Taking a look at join selectivity...
> > > > > > > For a query like this:
> > > > > > >
> > > > > > > SELECT id FROM table1, table2
> > > > > > > WHERE table1.geom && table2.geom;
> > > > > > >
> > > > > > > RESTRICT selectivity is invoked twice and
> > > > > > > JOIN selectivity is invoked once.
> > > > > > > The RESTRICT code is not able to find a costant
> > part and thus
> > > > > > > returns the default value (0.000005), JOIN
> > selectivity so far
> > > > > > > returns an hard-wired 0.1.
> > > > > > >
> > > > > > > Questions:
> > > > > > > (1) What should RESTRICT selectivity do in this case ?!
> > > > >
> > > > > > Maybe that's how the planner decide what to do:
> > > > > > 1) sequencially scan table1 and use index for each row
> > > > > > (RESTRICT)
> > > > > > 2) sequencially scan table2 and use index for each row
> > > > > > (RESTRICT)
> > > > > > 3) ... some other magic I'm missing .. (JOIN)
> > > > >
> > > > > Indeed, you could be on the right lines here in thinking
> > > > the planner
> > > > > considers some form of individual scan on each first before
> > > > finalising
> > > > > on a plan type (although unless the tables are small I
> > would have
> > > > > thought this would not have been an option). Does this
> > > > change if you
> > > > > do a SET ENABLE_SEQSCAN = 'f' before the query?
> > > >
> > > > Bingo.
> > > > Both ENABLE_SEQSCAN = 'f' or unavailability of an index make
> > > > the selectivity estimator calls go away. The join selectivity
> > > > is called nonetheless (also in absence of indexes).
> > >
> > > Right. So what you're saying is that if there is *no* GiST index on
> > > *one* of the geom columns, or sequential scans are
> > disabled, then the
> > > calls to RESTRICT go away?
> >
> > Index on a single table makes 2 calls to RESTRICT:
> >
> > strk=# select * from test1, test2 where test1.geom && test2.geom;
> > NOTICE: LWGEOM_gist_joinsel called (returning 0.000005)
> > NOTICE: LWGEOM_gist_sel called
> > NOTICE: no constant arguments - returning default selectivity
> > NOTICE: LWGEOM_gist_sel called
> > NOTICE: no constant arguments - returning default selectivity
> >
> > Index on a both table makes 4 calls to RESTRICT:
> >
> > strk=# select * from test1, test2 where test1.geom && test2.geom;
> > NOTICE: LWGEOM_gist_joinsel called (returning 0.000005)
> > NOTICE: LWGEOM_gist_sel called
> > NOTICE: no constant arguments - returning default selectivity
> > NOTICE: LWGEOM_gist_sel called
> > NOTICE: no constant arguments - returning default selectivity
> > NOTICE: LWGEOM_gist_sel called
> > NOTICE: no constant arguments - returning default selectivity
> > NOTICE: LWGEOM_gist_sel called
> > NOTICE: no constant arguments - returning default selectivity
> >
> > No index makes no calls to RESTRICT:
> >
> > strk=# select * from test1, test2 where test1.geom && test2.geom;
> > NOTICE: LWGEOM_gist_joinsel called (returning 0.000005)
> >
> > >
> > > > > It just seems strange for a <column> <operator>
> > <column> clause to
> > > > > call a function involving a constant. Again, I'd
> > probably ask on
> > > > > pgsql-hackers just to clarify - I think Tom Lane was
> > > > involved with the
> > > > > planner, so will be able to answer this one fairly quickly.
> > >
> > > CCd to pgsql-hackers for clarification. BTW which version are you
> > > developing against - 7.4 or 8.0?
> >
> > 8.0.0RC1
> >
> > --strk;
> >
> > >
> > >
> > > Kind regards,
> > >
> > > Mark.
> > >
> > > ------------------------
> > > WebBased Ltd
> > > South West Technology Centre
> > > Tamar Science Park
> > > Plymouth
> > > PL6 8BT
> > >
> > > T: +44 (0)1752 791021
> > > F: +44 (0)1752 791023
> > > W: http://www.webbased.co.uk
> > >
> >
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
More information about the postgis-devel
mailing list