Re: Index use with left join
От | Julian Scarfe |
---|---|
Тема | Re: Index use with left join |
Дата | |
Msg-id | 014001c53ce9$f1772110$0600a8c0@Wilbur обсуждение исходный текст |
Ответ на | Index use with left join ("Julian Scarfe" <julian@avbrief.com>) |
Список | pgsql-general |
> "Julian Scarfe" <julian@avbrief.com> writes: >> Does the planner "realise" that >> the intersection, Query 6, will still return 150 rows, or does it assume >> independence of the filters in some way and estimate >> 20,000*(150/20,000)*(396/20,000)? From: "Tom Lane" <tgl@sss.pgh.pa.us> > It assumes independence of the conditions --- which is why having two > of them reduced the rowcount estimate so much. There are some limited > cases in which it can recognize redundant conditions, but offhand I > think that only works for scalar inequalities (like "x < 5 AND x < 6"). Even that's smarter than I dared hope for! >> Any general suggestions for workarounds? > > Not much, other than trying to avoid redundant conditions. > > Did you look into the state of the PostGIS work on geometric statistics? No, though PostGIS is clearly the way forward for my needs in the medium/long term. PostGIS stores bounding boxes for its geometric features. The operators like && and @ work as intersect and containment for the bounding boxes, while Intersects() and Contains() use more exact but presumably computationally expensive functions. I don't yet know how these, GiST indexes and the planner get along together. But I imagine the issue I've come across is one of the, if not the, most important one in spatially enabled databases. Thanks again Julian
В списке pgsql-general по дате отправления: