From: | strk(at)refractions(dot)net |
---|---|
To: | Mark Cave-Ayland <m(dot)cave-ayland(at)webbased(dot)co(dot)uk> |
Cc: | pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net |
Subject: | Re: join selectivity |
Date: | 2004-12-13 11:27:51 |
Message-ID: | 20041213112751.GA2665@freek.keybit.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Dec 13, 2004 at 10:16:09AM -0000, Mark Cave-Ayland wrote:
>
> > -----Original Message-----
> > From: strk [mailto:strk(at)keybit(dot)net]
> > Sent: 10 December 2004 15:35
> > To: Mark Cave-Ayland
> > Cc: postgis-devel(at)postgis(dot)refractions(dot)net
> > Subject: join selectivity
> >
> >
> > 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 ?!
> > (2) Is JOIN selectivity a fraction of table2 X table1
> > records ?
>
>
> Hi strk,
>
> Really??! I can't see why the RESTRICT selectivity should be called - the
> only thing I can think of is that it's being called as some part of cast or
> query rewriting.
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)
>
> Hmmm good question - the wording in the documentation is "The idea behind a
> join selectivity estimator is to guess what fraction of the rows in a pair
> of tables will satisfy a WHERE-clause condition of the form" which is
> slightly ambiguous - I would ask on pgsql-hackers now that the mailing lists
> are working normally again.
I've tested this. It is a fraction of table2.rows X table1.rows.
0.1 is probably a big number for that...
--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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Cave-Ayland | 2004-12-13 12:16:15 | Re: join selectivity |
Previous Message | Simon Riggs | 2004-12-13 09:11:23 | Re: [Testperf-general] BufferSync and bgwriter |