[postgis-devel] RE: join selectivity
strk at refractions.net
strk at refractions.net
Tue Dec 14 03:01:43 PST 2004
I've rebuilt the db and still get same results.
--strk;
On Tue, Dec 14, 2004 at 11:22:44AM +0100, strk at refractions.net wrote:
> 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