Re: join selectivity

From: strk <strk(at)keybit(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark Cave-Ayland <m(dot)cave-ayland(at)webbased(dot)co(dot)uk>, pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net
Subject: Re: join selectivity
Date: 2004-12-14 11:24:01
Message-ID: 20041214112401.GF9888@freek.keybit.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Dec 13, 2004 at 12:16:05PM -0500, Tom Lane wrote:
> "Mark Cave-Ayland" <m(dot)cave-ayland(at)webbased(dot)co(dot)uk> writes:
> > 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.
>
> Hm, are you testing in a context where both tables have indexes that are
> relevant to the && operator?

Single index relevant to the && operator makes 2 calls to RESTRICT.
Double index (one for each table) makes 4 calls to RESTRICT.
In both cases JOIN is called once.

--strk;

> The estimated join result size is computed from the join selectivity
> estimate for the && operator. I was about to say that restriction
> selectivity wouldn't be used at all, but on second thought I believe
> that it would be invoked while considering nestloop with inner indexscan
> plans. That is, we'd consider
>
> NestLoop
> Seq Scan on table2
> Indexscan on table1
> IndexCond: table1.geom && outer.geom
>
> and to determine the estimated cost of each indexscan, we would invoke
> restriction selectivity for &&, with varRelid referencing table1.
> Given this call you are supposed to treat table2.geom as a constant of
> uncertain value, so the thing is semantically sensible as a restriction
> clause for table1 (whether you can produce a really good estimate is
> another question :-().
>
> Similarly, we'd consider the reverse plan with table1 as outer, and
> that would give rise to another restriction selectivity check with
> varRelid = table2.
>
> >> (2) Is JOIN selectivity a fraction of table2 X table1
> >> records ?
>
> Yes. Similarly restriction selectivity is a fraction of records in the
> table under consideration.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-12-14 12:48:06 Threading fix for AIX
Previous Message strk 2004-12-14 10:49:39 800RC1 valgrind-detected bug ?