[postgis-users] Indexes

Fri May 30 04:50:50 PDT 2014

Thanks for the useful info Paul - that makes sense and I understand now.

I will run autovacuum's on each of the slaves from now on, and try 
increasing RAM as much as I can (the db is ~30Gb and growing).

Cheers,

Graham

On 29/05/2014 20:00, postgis-users-request at lists.osgeo.org wrote:
> Send postgis-users mailing list submissions to
> 	postgis-users at lists.osgeo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> 	http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> 	postgis-users-request at lists.osgeo.org
>
> You can reach the person managing the list at
> 	postgis-users-owner at lists.osgeo.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
>     1. Re: Indexes (Paul Ramsey)
>     2. ERROR: rt_raster_from_two_rasters: (Jason Mathis)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 29 May 2014 09:59:34 -0700
> From: Paul Ramsey <pramsey at cleverelephant.ca>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Indexes
> Message-ID:
> 	<CACowWR3D7v83YoZC8iWSkSv=GZ7oS4ycwqaTkEE9a-gx4s_zsw at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Running queries does not build up an index, you CREATE INDEX, and
> that's it, it's built.
>
> The time differences you are seeing are almost certainly the result of
> data being transferred up into the operating system's virtual file
> system (VFS) from the disk. Reading from disk is slow; reading from
> memory is fast; to try and take advantage of RAM, the operating system
> caches frequently read disk pages in RAM. So, your first slow query is
> going all the way to disk, the second (and subsequent) query is able
> to take advantage of the cached data.
>
> I'm pretty sure that a VACUUM command doesn't get transferred to the
> slave replicas, but I could be wrong. In any event the slaves should
> have their own autovacuum settings in their own configs and be
> gathering stats and updating dead pages on their own.
>
> VACUUM and ANALYZE are only going to make differences performance-wise
> for data that is changing enough to (a) create a lot of dead tuples
> and (b) to change the distribution of values of data in the tables.
>
> All the forgoing to say: you're just seeing the difference between a
> "cold" (uncached) and "hot" (cached) query and then making a lot of
> (incorrect) inferences about indexes from that observation. If your
> data is large enough that it doesn't fit all in RAM, you'll find that
> as you bounce around the data set with queries some queries will be
> "hot" (they'll have been cached by a previous query) and some will be
> "cold", but that each query will cause different bits of data to be
> swapped in and out of memory as the operating system does its best to
> keep the "most useful" bits of data in RAM.
>
> P.
>
> On Wed, May 28, 2014 at 10:29 AM, Graham Glanfield
> <graham.glanfield at geocento.com> wrote:
>> Hi folks,
>>
>> I have streaming replication running between one master and a few slaves
>> (Fedorax_86_64 - PostgreSQL 9.2.6 - POSTGIS 2.1.2).
>> My application only ever reads from a slave, and I have a couple of
>> questions with regards to indexes and vacuuming.
>> It is my understanding (probably incorrect) that any vacuuming executed on
>> the master gets propagated to the slaves as part of the streaming
>> replication process.
>>
>> My main question is if I run a spatial query against a slave the first time,
>> it may take 3 seconds, then the 2nd time it is near instant (if I query the
>> same slave), so I'm assuming that the slave index/stats must be being
>> updated? Vacuum is set to run once per night on the master, so does this
>> mean that if I ran the query on that slave the next morning, it would take 3
>> seconds again?
>>
>> On a similar point, I have a background process that creates dynamic spatial
>> queries that I hope will help build up the GIST index, and increase the
>> speed of users spatial queries, but I don't know whether to run these
>> queries against the master or on each of the slaves?
>>
>> Best regards,
>>
>> Graham
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> ------------------------------
>
> Message: 2
> Date: Thu, 29 May 2014 12:45:02 -0600
> From: Jason Mathis <jmathis at redzonesoftware.com>
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] ERROR: rt_raster_from_two_rasters:
> Message-ID: <etPan.5387802f.625558ec.92f3 at palos>
> Content-Type: text/plain; charset="utf-8"
>
> I loaded a bunch raster data into separate tables. One for each us western state. I thought it maybe handy to query a view instead of the individual tables. Anyways everything seemed ok but I started to get errors on a few ids I was testing. I knew what table it should be pulling from so I queried the table and no issues. So i must assume the error is because of the ?union all? in the view. Can anyone confirm this and why? I am on postgres 9.2.8 and postgis 2.1.2.?
>
> thanks,
> jason
>
> query>>>
>
> 	SELECT
> ? ? ? ? 	report_id, incident_id,?
> ? ? ? ? 	ST_SummaryStats(
> ? ? ? ? ? 	ST_UNION(
> ? ? ? ? ? ? 	ST_Clip(c.rast, 1,?
> ? ? ? ? ? ? 	ST_Transform(w.geom, 5070), true)
> ? ? ? ? ? 	),
> ? ? ? ? ? 	1
> ? ? ? ? 	)?as hdesc
> 	FROM rasters c
> 	JOIN w_reports w?
> ? ? ? ? 	ON ST_Intersects(c.rast, ST_Transform(w.geom, 5070)) ?
> 	where w.incident_id = 5443?
> 	group by incident_id, report_id
>
> error>>>
>
> 	ERROR:? rt_raster_from_two_rasters: The two rasters provided do not have the same alignment
>
> 	********** Error **********
>
> 	ERROR: rt_raster_from_two_rasters: The two rasters provided do not have the same alignment
> 	SQL state: XX000
>
>
>
>
>
>
>
>