Search Postgresql Archives

Re: Index use with left join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Julian Scarfe" <julian@xxxxxxxxxxx> 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@xxxxxxxxxxxxx>

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




---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux