Re: Slow query on primary server runs fast on hot standby

On Fri, Jul 15, 2016 at 4:17 PM Kaixi Luo <kaixiluo@gmail.com> wrote:

Hello,

I have a primary PostgreSQL server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 16GB of RAM.

Do you have different hardware configuration for master and standby? I am not sure if that is the right thing to do. I usually prefer them to be equally sized and have same configuration. But may be someone else with more experience can comment better on that aspect.

 

My problem is as follows: I've detected a query that takes a long time to run on my primary server but runs very fast on the standby server. I did an EXPLAIN ANALYZE on the query:

What is the version of PostgreSQL on both servers? Check with 

select version();

EXPLAIN ANALYZE

SELECT this_.id AS id1_31_0_,

       this_.nom AS nom2_31_0_,

       this_.nom_slug AS nom3_31_0_,

       this_.descripcio AS descripc4_31_0_,

       this_.url AS url5_31_0_,

       this_.data_captura AS data6_31_0_,

       this_.data_publicacio AS data7_31_0_,

       this_.propietari AS propieta8_31_0_,

       this_.privacitat AS privacit9_31_0_,

       this_.desnivellpujada AS desnive10_31_0_,

       this_.desnivellbaixada AS desnive11_31_0_,

       this_.longitud AS longitu13_31_0_,

       this_.beginpoint AS beginpo14_31_0_,

       this_.endpoint AS endpoin15_31_0_,

       this_.caixa3d AS caixa16_31_0_,

       this_.pic_id AS pic17_31_0_,

       this_.skill AS skill18_31_0_,

       this_.spatial_type AS spatial19_31_0_,

       this_.tags_cached AS tags20_31_0_,

       this_.images_cached AS images21_31_0_,

       this_.ncomments AS ncommen22_31_0_,

       this_.group_order AS group23_31_0_,

       this_.author AS author24_31_0_,

       this_.proper_a AS proper25_31_0_,

       this_.duration AS duratio26_31_0_,

       this_.isloop AS isloop27_31_0_,

       this_.seo_country AS seo28_31_0_,

       this_.seo_region AS seo29_31_0_,

       this_.seo_place AS seo30_31_0_,

       this_.source AS source31_31_0_,

       this_.source_name AS source32_31_0_,

       this_.api_key AS api33_31_0_,

       this_.ratingui AS ratingu34_31_0_,

       this_.nratings AS nrating35_31_0_,

       this_.trailrank AS trailra36_31_0_,

       this_.ncoords AS ncoords37_31_0_,

       this_.egeom AS egeom38_31_0_,

       this_.elevels AS elevels39_31_0_,

       this_.elevations AS elevati40_31_0_,

       this_.nphotoswpts AS nphotos41_31_0_,

       this_.nfavourited AS nfavour42_31_0_,

       this_.ncompanions AS ncompan43_31_0_,

       this_.group_id AS group44_31_0_

FROM spatial_artifact this_

WHERE this_.group_id IS NULL

  AND this_.propietari=7649

ORDER BY this_.id DESC LIMIT 20

--PRIMARY SERVER (EXPLAIN ANALYZE output)

"Limit  (cost=0.43..22734.71 rows=20 width=604) (actual time=1804.124..293469.085 rows=20 loops=1)"

"  ->  Index Scan Backward using "PK_SPATIAL_ARTIFACT" on spatial_artifact this_  (cost=0.43..7776260.84 rows=6841 width=604) (actual time=1804.121..293469.056 rows=20 loops=1)"

"        Filter: ((group_id IS NULL) AND (propietari = 7649))"

"        Rows Removed by Filter: 2848286"

"Total runtime: 293469.135 ms"

--STANDBY SERVER (EXPLAIN ANALYZE output)

"Limit  (cost=23533.73..23533.78 rows=20 width=604) (actual time=2.566..2.569 rows=20 loops=1)"

"  ->  Sort  (cost=23533.73..23550.83 rows=6841 width=604) (actual time=2.566..2.567 rows=20 loops=1)"

"        Sort Key: id"

"        Sort Method: top-N heapsort  Memory: 35kB"

"        ->  Index Scan using idx_own_spas on spatial_artifact this_  (cost=0.43..23351.70 rows=6841 width=604) (actual time=0.037..2.119 rows=618 loops=1)"

"              Index Cond: (propietari = 7649)"

"Total runtime: 2.612 ms"

I've run ANALYZE on my table and have reindexed the index idx_own_spas on my primary server, but it hasn't helped.

Here is the postgresql config of my two servers:

--PRIMARY SERVER (postgresql.conf)

shared_buffers = 8GB

work_mem = 42MB

maintenance_work_mem = 2GB

effective_cache_size = 44GB

what are the values of random_page_cost and seq_page_cost? 

Also what might help here is the number of rows and pages in the table -

select relpages,reltuples, relname from pg_class where relname in ('idx_own_spas ','spatial_artifact','PK_SPATIAL_ARTIFACT');

--STANDBY SERVER (postgresql.conf)

shared_buffers = 800MB

work_mem = 20MB

maintenance_work_mem = 128MB

effective_cache_size = 1024MB

 

Could you shed some light into why this is happening? Thank you.

Cheers,

Kaixi

--

--

Best Regards

Sameer Kumar | DB Solution Architect 

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com