Re: What's a good way to improve this query? - Mailing list pgsql-general
From | Paul Ramsey |
---|---|
Subject | Re: What's a good way to improve this query? |
Date | |
Msg-id | 92E38CCEA1404E04B76494D2DCD7A572@cleverelephant.ca Whole thread Raw |
In response to | What's a good way to improve this query? (Jorge Arévalo <jorgearevalo@libregis.org>) |
Responses |
Re: What's a good way to improve this query?
(Jorge Arévalo <jorgearevalo@libregis.org>)
|
List | pgsql-general |
Well, your objects are larger than the page size, so you're getting them out of the toast tables, not directly out of mainstorage. You may also have your type declared as 'main' storage, which means it's zipped up, so it's being unzipped beforeyou can access it, that's also an overhead. For metadata retrieval, the thing to do is store the metadata at the head of the object (which I'm not looking into pgrasterto see if you do, but I'll assume for now) and then use PG_DETOAST_DATUM_SLICE in the metadata accessor function,so that you only pull the bytes you want, rather than detoasting the whole object just to get the header information. You may be causing further pain by having all the metadata functions separate, so that in fact the object is being read 9separate times by your different functions. It'll float into cache quickly enough, but the uncompression step at each accesswill still be there. You might want to stuff the query through a sampling profiler (OSX Shark!) and confirm, but Iwould guess you'll find a lot of cycles spinning in zlib for this query. Paul -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Wednesday, June 5, 2013 at 11:22 AM, Jorge Arévalo wrote: > Hello, > > I'm running this PostGIS Raster query > > select > st_scalex(rast), > st_scaley(rast), > st_skewx(rast), > st_skewy(rast), > st_width(rast), > st_height(rast), > rid, > st_upperleftx(rast), > st_upperlefty(rast), > st_numbands(rast) > from > my_postgis_raster_table > > > > I want to remark that, even when 'rast' is a complex type and can be really big, I'm getting just metadata. Not the whole'rast' column. Anyway, the average dimensions of a 'rast' column in like 600x400 pixels (8 bits per pixel). So, notso big (about 234 KB per rast object). > > My table has 1257 rows, and this query takes about 45 secs to execute (45646 msecs). I think it's too slow. I'm just gettingmetadata, not the whole 'rast' object, as said. > > This is the explain analyze output > > Seq Scan on my_postgis_raster_table (cost=0.00..198.85 rows=1257 width=36) (actual time=86.867..51861.495 rows=1257 loops=1) > Total runtime: 51863.919 ms > > > > So, basically a sequential scan. As expected, I guess (I'm not a postgres expert, so sorry if I'm talking nonsense) > > I've calculated the effective transfer rate for this table > > SELECT pg_size_pretty(CAST(pg_relation_size('my_postgis_raster_table') / 45646 * 1000 as int8)) AS bytes_per_second; > > As it's 27KB. Isn't it a slow rate? Is there any kind of index I could create to speed this query? Maybe use some kindof cache system? > > Many thanks in advance, > > -- > Jorge Arevalo > Freelance developer > > http://www.krop.com/jorgearevalo > http://about.me/jorgeas80 > > Enviado con Sparrow (http://www.sparrowmailapp.com/?sig) > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org (mailto:pgsql-general@postgresql.org)) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: