[postgis-devel] RE: && GIST - explain plan

[Mark Cave-Ayland]

> -----Original Message-----
> From: Carl Anderson [mailto:carl.anderson at co.fulton.ga.us] 
> Sent: 08 March 2004 14:42
> To: postgis-devel at postgis.refractions.net; 
> m.cave-ayland at webbased.co.uk
> Subject: && GIST - explain plan
> 
> 
> moved from postgis-users
> 
> Mark,
> here are the explain analyze verbose plans you asked for.
> 
> I have got the same behavior on a
>    dual P3 linux 2.4.24
>    Postgres 7.3.4  Postgis 0.7.5
> and prior versions
> 
> and  a
>   dual P4 linux 2.6.3
>    Postgres 7.4.1  Postgis 0.8
> ---
> 
> I have been noticing this issue for some time, but it had not 
> aggravated 
> me enough to seek a solution.
> 
> The statements I sent use a exists clause because it is the 
> quickest way 
> to overalloc memory.  I actually use a not exists clause 
> which I thought 
> would complicate the expression of the problem.
> 
> ----
> I had to add  a geo_oid limit to both queries to get them to run.  In 
> both statements attached  the peak memory alloc was 2.7G.
> 
> My first suspicion is that the GIST selection routine is not freeing 
> memory during its run and leaving it to the postmaster to 
> cleanup after 
> the completion of the statement.

Hi Carl,

Thanks for this. I wanted to eliminate that it was the fact that the
query was part of a WHERE clause that was causing the problem, but that
doesn't seem to be the case. A couple more questions: i) what options
did you use to compile PostGIS, e.g. USE_STATS, USE_GEOS etc. and ii)
are you able to attach a gdb instance to the process and do a bt to see
exactly where the query is spending its time?


Cheers,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.