Re: Postgres scalability and performance on windows

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 && '0103000020E6100000010000000500000058631EDF87ECC1BF608F3D1911694940A0958A8763C9C1BF535069BA846C494026B5F1284FABB8BFAB1577356E6C494094E1170D33F3B8BF7700CC99FA68494058631EDF87ECC1BF608F3D1 (..)"

"                                            ->  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 && '0103000020E6100000010000000500000058631EDF87ECC1BF608F3D1911694940A0958A8763C9C1BF535069BA846C494026B5F1284FABB8BFAB1577356E6C494094E1170D33F3B8BF7700CC99FA68494058631EDF87ECC (..)"

"                                      ->  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