On Fri, Sep 26, 2014 at 9:06 AM, Simon Riggs wrote:
> If we can at least agree it is a problem, we can try to move forwards.
Well that's a good question. I don't think we do and I think the
reason why is because we haven't actually pinned down exactly what is
the problem.
The real problem here i
I changed the query from (st_within or st_touches) to ST_intersects, that sped
up the execution. Reference progress in Attachment please.
Thanks
From: Graeme B. Bell [g...@skogoglandskap.no]
Sent: Monday, September 29, 2014 7:08 AM
To: Burgess, Freddie
Cc:
On 30/09/14 12:00, Tom Lane wrote:
Simon Riggs writes:
The way I'm seeing it, you can't assume the LIMIT will apply to any
IndexScan that doesn't have an index condition. If it has just a
filter, or nothing at all, just an ordering then it could easily scan
the whole index if the stats are wron
Simon Riggs writes:
> The way I'm seeing it, you can't assume the LIMIT will apply to any
> IndexScan that doesn't have an index condition. If it has just a
> filter, or nothing at all, just an ordering then it could easily scan
> the whole index if the stats are wrong.
That statement applies wit
On 09/26/2014 01:06 AM, Simon Riggs wrote:
> On 23 September 2014 00:56, Josh Berkus wrote:
>
>> We've hashed that out a bit, but frankly I think it's much more
>> profitable to pursue fixing the actual problem than providing a
>> workaround like "risk", such as:
>>
>> a) fixing n_distinct estima
On 29 September 2014 16:00, Merlin Moncure wrote:
> On Fri, Sep 26, 2014 at 3:06 AM, Simon Riggs wrote:
>> The problem, as I see it, is different. We assume that if there are
>> 100 distinct values and you use LIMIT 1 that you would only need to
>> scan 1% of rows. We assume that the data is arra
Hi,
Two things:
- Make sure you are creating a GIST index on your geometry column in postgis.
- Try using st_intersects rather than &&. I've noticed that && isn't using
indices correctly in some situations e.g. function indices for st_transform'd
geo columns.
Graeme
On 26 Sep 2014, at 18:17
On Fri, Sep 26, 2014 at 3:06 AM, Simon Riggs wrote:
> The problem, as I see it, is different. We assume that if there are
> 100 distinct values and you use LIMIT 1 that you would only need to
> scan 1% of rows. We assume that the data is arranged in the table in a
> very homogenous layout. When da
