From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Mark Cave-Ayland" <m(dot)cave-ayland(at)webbased(dot)co(dot)uk> |
Cc: | strk(at)refractions(dot)net, pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net |
Subject: | Re: join selectivity |
Date: | 2004-12-13 17:16:05 |
Message-ID: | 25919.1102958165@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
"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?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2004-12-13 18:24:19 | Re: possible wierd boolean bug? |
Previous Message | strk | 2004-12-13 17:13:29 | Re: [postgis-devel] RE: join selectivity |