SEO

Hi,

Thanks for your suggestions. Here's an output of the explain analyse.
I'll change the shared_buffers and look at the behaviour again.

"Limit (cost=59.53..59.53 rows=1 width=28) (actual time=15.681..15.681
rows=1 loops=1)"
" -> Sort (cost=59.53..59.53 rows=1 width=28) (actual
time=15.678..15.678 rows=1 loops=1)"
" Sort Key: ceil(subq.percentcover), (1::double precision /
ds.maxresolution)"
" -> Hash Join (cost=58.19..59.52 rows=1 width=28) (actual
time=15.630..15.663 rows=2 loops=1)"
" Hash Cond: ("outer".datasetid = "inner".did)"
" -> Seq Scan on tbl_metadata_dataset ds (cost=0.00..1.21
rows=21 width=24) (actual time=0.006..0.021 rows=21 loops=1)"
" -> Hash (cost=58.18..58.18 rows=1 width=12) (actual
time=15.591..15.591 rows=2 loops=1)"
" -> Sort (cost=58.17..58.17 rows=1 width=117)
(actual time=15.585..15.586 rows=2 loops=1)"
" Sort Key: tbl_metadata_chunks.datasetid"
" -> HashAggregate (cost=58.13..58.16 rows=1
width=117) (actual time=15.572..15.573 rows=2 loops=1)"
" -> Hash IN Join (cost=3.34..58.10
rows=7 width=117) (actual time=0.261..0.544 rows=50 loops=1)"
" Hash Cond: ("outer".datasetid =
"inner".datasetid)"
" -> Bitmap Heap Scan on
tbl_metadata_chunks (cost=2.05..56.67 rows=14 width=117) (actual
time=0.204..0.384 rows=60 loops=1)"
" Filter: (chunkgeometry &&
'0103000020E6100000010000000500000058631EDF87ECC1B F608F3D1911694940A0958
A8763C9C1BF535069BA846C494026B5F1284FABB8BFAB15773 56E6C494094E1170D33F3B
8BF7700CC99FA68494058631EDF87ECC1BF608F3D1 (..)"
" -> Bitmap Index Scan on
tbl_metadata_chunks_idx3 (cost=0.00..2.05 rows=14 width=0) (actual
time=0.192..0.192 rows=60 loops=1)"
" Index Cond:
(chunkgeometry &&
'0103000020E6100000010000000500000058631EDF87ECC1B F608F3D1911694940A0958
A8763C9C1BF535069BA846C494026B5F1284FABB8BFAB15773 56E6C494094E1170D33F3B
8BF7700CC99FA68494058631EDF87ECC (..)"
" -> Hash (cost=1.26..1.26
rows=10 width=4) (actual time=0.037..0.037 rows=10 loops=1)"
" -> Seq Scan on
tbl_metadata_dataset (cost=0.00..1.26 rows=10 width=4) (actual
time=0.005..0.024 rows=10 loops=1)"
" Filter: (typeofdataid
= 1)"
"Total runtime: 15.871 ms"



Gopal

"Gopal" <gopal@getmapping.com> writes:
> Thanks for your suggestions. Here's an output of the explain analyse.

What's the query exactly, and what are the schemas of the tables it
uses (psql \d descriptions would do)?

The actual runtime seems to be almost all spent in the hash aggregation
step:

> -> HashAggregate (cost=58.13..58.16 rows=1 width=117) (actual time=15.572..15.573 rows=2 loops=1)
> -> Hash IN Join (cost=3.34..58.10 rows=7 width=117) (actual time=0.261..0.544 rows=50 loops=1)

15 msec seems like a long time to aggregate only 50 rows, so I'm
wondering what aggregates are being calculated and over what
datatypes...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq