Dear all,
Can anyone Please guide me with some suggestions on how to tune the
below query as I needed to perform the below query as faster as i can.
I have 3 tables on which the query runs:
pdc_uima=# select
pg_size_pretty(pg_total_relation_size('page_content_demo'));
pg_size_pretty
----------------
1260 MB
pdc_uima=# select
pg_size_pretty(pg_total_relation_size('metadata_demo'));
pg_size_pretty
----------------
339 MB
pdc_uima=# select
pg_size_pretty(pg_total_relation_size('loc_context_demo'));
pg_size_pretty
----------------
345 MB
My Query is :
explain analyze select
m.doc_category,p.heading,l.lat,l.lon,p.crawled_page_url,p.category,p.dt_stamp,p.crawled_page_id,p.content
from loc_context_demo l,page_content_demo p,metadata_demo m where
l.source_id=p.crawled_page_id and m.doc_id=l.source_id and
st_within(l.geom,GeomFromText('POLYGON((19.548124415111626
73.21900819489186,19.548124415111626 73.21900819489186,19.55011196668719
73.21994746420259,19.552097947014058 73.22087843652453,19.55408236353752
73.2218011513938,19.588219714571828 75.1654223522423,19.599133094249137
76.46053245473952,19.57365361244478 79.69902443272414,19.68652202327923
82.74135922990342,19.56446013085233 85.15028561045767,19.551174510964337
85.37052962767306,19.553500408319763 85.37198146688313,19.55582660405639
85.37341757236464,19.55815307123746 85.37483800206365,19.56047978332553
85.37624281337641,19.562806714176496 85.37763206315,19.565133838033702
85.37900580768307,19.567461129522137 85.38036410272655,19.56978856364264
85.3817070034843,19.572116115766228 85.38303456461405,19.56649262333915
85.15194545531163,18.773772341648947 84.46107113406764,17.95738291093396
84.21223929994393,16.939045429366846
83.74699366402301,15.915601954028702
83.28824222570091,14.692125537681664
82.40657922201932,13.869583501048409 81.75586112437654,13.23910975048389
81.53550253438608,12.607561680274236
81.31596402018643,11.960089890060914 81.3105660302366,11.961002716398268
81.3118121189388,11.102247999047648 81.09276935832209,10.230582572954035
81.08704044732613,9.364677626102125 80.87125821859627,8.484379037020355
80.65888115596269,7.5953685679122565 80.44798762937165,6.678959105840814
80.44990760581172,5.756074889890018 80.24361993771154,5.756819343429733
80.2442993962505,5.757563827399336 80.24498070122854,5.758308340445826
80.24566385572928,4.83232192901788 80.03636862497382,4.832964922142748
80.0371046690356,4.833608089257533 80.0378393944808,4.834251429338765
80.038572803232,4.834894941366702 80.03930489720865,4.835538624325311
80.04003567832711,5.575253995307823 78.3586811224377,5.82022779480326
77.52223682832437,6.9742086723828365
76.89564878408815,7.6455592543043425 76.26930608306816,8.761889779304363
75.43381068367601,10.059251343658966 74.3840274150521,11.136283050704487
73.75034557867339,12.187315498051541
72.89986083146191,13.242658350472773
72.46589681727389,14.721187899066917
72.23365448169334,16.384503005199107
71.77586874336029,17.834343858181125
71.52762561326514,18.868652843809762
71.49887565337562,19.487812049094533
71.48086802014905,19.489698327426513 71.48186192551053,19.89987693684175
71.46838407646581,20.310716259621934 71.454517020832,20.312680952069726
71.45872696349684,20.314637217119998 71.46296731473512,20.31658488533959
71.46723821288163,20.318523784696943 71.47153979566505,20.53302678388929
71.88565153869924,20.767109171722186
72.75373018504017,20.791013365997372 73.62713545368305,20.79185810562998
73.6280821559539,20.79269895778539 73.62902276312589,20.793535942149113
73.6299573226539,20.79436907831312 73.63088588154903,20.795198385776008
73.6318084863835,20.796023883943136 73.63272518329538,20.796845592126836
73.6336360179933,20.79766352954653 73.63454103576112,20.798477715328943
73.63544028146251,20.799288168508316 73.6363337995455,20.80009490802656
73.63722163404697,20.800897952733482
73.63810382859708,19.980139052593813 74.07773531285727,19.98131962229422
74.0780344216337,19.982501271580563 74.078336024665,19.983684009372077
74.07864013150498,19.98486784461094 74.07894675180037,19.98605278626243
74.07925589529141,19.987238843315097
74.07956757181258,19.988426024780967
74.07988179129316,19.548124415111626 73.21900819489186))',4326)) and
m.doc_category='Naxalism'order by p.dt_stamp desc;
Today in the morning , I am shocked to see the result below :
Sort (cost=129344.37..129354.40 rows=4013 width=1418) (actual
time=21377.760..21378.441 rows=4485 loops=1)
Sort Key: p.dt_stamp
Sort Method: quicksort Memory: 7161kB
-> Nested Loop (cost=44490.85..129104.18 rows=4013 width=1418)
(actual time=267.729..21353.703 rows=4485 loops=1)
-> Hash Join (cost=44490.85..95466.11 rows=3637 width=73)
(actual time=255.849..915.092 rows=4129 loops=1)
Hash Cond: (l.source_id = m.doc_id)
-> Seq Scan on loc_context_demo l (cost=0.00..47083.94
rows=16404 width=18) (actual time=0.065..628.255 rows=17072 loops=1)
Filter: ((geom &&
'0103000020E6100000010000005C000000270BB5E1518C334075A7F23A044E5240270BB5E1518C334075A7F23A044E52404A0F4A23D48C3340A66
5879E134E5240379D824A568D3340ED504FDF224E52401DAF7E57D88D3340E6DC74FD314E5240CEF23491959633401E3AA24796CA524057C055C96099334095F61D5D791D5340BEAE90F6DA923340
EE69F9D0BCEC5340D2F745E8BFAF334078C1FB6D72AF544019E8897580903340687E89479E4955400AFBD2C5198D33408343E6C1B6575540580EE833B28D3340CBBE5A8BCE5755403FABFEA64A8E3
340B443D112E65755407920A31EE38E3340540A8858FD575540CB73639A7B8F3340A1B3BC5C14585540765BCF19149033409E49AC1F2B585540BC37789CAC9033408B3F93A1415855407E0CF12145
9133401B72ADE257585540DE7ACEA9DD913340A12736E36D58554002BBA63376923340511068A383585540BAAA1AA905913340B7556E79B94955407BEEB5F115C63240CF7C8030821D5540EC35E40
B17F53140D90B2554950D5540C196004865F03040135383BECEEF5440F86981C7C9D42F408C2D858F72D2544069234A475E622D409773DB64059A5440C749740C3ABD2B40F6605607607054404FF7
DC976C7A2A4041076CAC456254404B29165312372940755A27C1385454405010EEE690EB274058C75750E05354406F984C8C08EC2740AC55D1BAF453544070FB87D9593426401D04E4EEEF455440F
0BA43EB0E7624407AAC18129245544012629B06B7BA224080CFD4B1C2375440A5BD758700F82040DE33DE1B2B2A54407CFF404CA8611E407C4A4ED4AB1C54407CA14B0E41B71A40B64B4549CB1C54
40229EF57E38061740FA471478970F5440280B64A6FB0617400555EF99A20F54403E40DDCFBE07174060FB88C3AD0F5440D4FE49FB81081740A88AE4F4B80F5440708023334C5413403CB711DD530
25440EE45ADC1F45413405B0243EC5F0254402A2BE45B9D551340E972ECF56B025440E19AB60146561340601910FA77025440341013B3EE5613405D05B0F8830254409216E86F97571340A445CEF1
8F02544035F622620F4D164000A4AAA1F496534001CD87CBE9471740E09A04546C61534098744DF596E51B401717474F523953401D78337C0D951E4010D9944F3C115340AD89CA6A16862140CFC2E
28DC3DB5240C5842E31561E2440A31AB9E793985240BD8C5BE4C64526406D4676A905705240A55424D1E75F28402385E2519739524084C31EB73D7C2A401E60E240D11D5240D786518A3F712D40F3
5BED31F40E52403C8BF8C96E62304026AE5FD5A7F15140A381208F97D531405E60389EC4E1514086BD630860DE324036012694EDDF5140AD741D40E17C3340E00EA98AC6DE5140E9339DDE5C7D334
057D066D3D6DE5140F428BE555EE63340D2983401FADD51406269CD198B4F3440B9FC8ECE16DD5140BC38DFDB0B503440CA8056C85BDD514085A28D108C503440E0F9A841A1DD514003F100B50B51
344028F11A3BE7DD5140824556C68A513440364940B52DDE514052B27C71748834406197CA83AEF851402902454461C434405BB0871D3D30524058A819DA7FCA3440C6EEBDFC22685240D7C07A36B
7CA34407CC17F7F32685240F442A351EECA34402D04B1E84168524022BBE72B25CB344026AB843851685240FB4E9CC55BCB3440B8302D6F606852406DBD141F92CB34408E96DC8C6F685240E45EA4
38C8CB3440FE66C4917E68524082259E12FECB344054B6157E8D685240419D54AD33CC3440182401529C68524030EC190969CC344052DCB60DAB685240ADD23F269ECC3440CA9866B1B968524092A
B1705D3CC344044A23F3DC8685240756CF2A507CD3440B8D170B1D6685240406C9864EAFA3340D89D889DF98452403FAD44C337FB334042381684FE84524089B70D3485FB33403B021A7503855240
EAD919B7D2FB3340E43D9E70088552406A7C8F4C20FC3340EA46AD760D855240772095F46DFC3340AD92518712855240146151AFBBFC334075B095A21785524015F3EA7C09FD33409A4984C81C855
240270BB5E1518C334075A7F23A044E5240'::geometry) AND _st_within(geom,
'0103000020E6100000010000005C000000270BB5E1518C334075A7F23A044E5240270BB5E1518C334075A7F
23A044E52404A0F4A23D48C3340A665879E134E5240379D824A568D3340ED504FDF224E52401DAF7E57D88D3340E6DC74FD314E5240CEF23491959633401E3AA24796CA524057C055C96099334095
F61D5D791D5340BEAE90F6DA923340EE69F9D0BCEC5340D2F745E8BFAF334078C1FB6D72AF544019E8897580903340687E89479E4955400AFBD2C5198D33408343E6C1B6575540580EE833B28D334
0CBBE5A8BCE5755403FABFEA64A8E3340B443D112E65755407920A31EE38E3340540A8858FD575540CB73639A7B8F3340A1B3BC5C14585540765BCF19149033409E49AC1F2B585540BC37789CAC90
33408B3F93A1415855407E0CF121459133401B72ADE257585540DE7ACEA9DD913340A12736E36D58554002BBA63376923340511068A383585540BAAA1AA905913340B7556E79B94955407BEEB5F11
5C63240CF7C8030821D5540EC35E40B17F53140D90B2554950D5540C196004865F03040135383BECEEF5440F86981C7C9D42F408C2D858F72D2544069234A475E622D409773DB64059A5440C74974
0C3ABD2B40F6605607607054404FF7DC976C7A2A4041076CAC456254404B29165312372940755A27C1385454405010EEE690EB274058C75750E05354406F984C8C08EC2740AC55D1BAF453544070F
B87D9593426401D04E4EEEF455440F0BA43EB0E7624407AAC18129245544012629B06B7BA224080CFD4B1C2375440A5BD758700F82040DE33DE1B2B2A54407CFF404CA8611E407C4A4ED4AB1C5440
7CA14B0E41B71A40B64B4549CB1C5440229EF57E38061740FA471478970F5440280B64A6FB0617400555EF99A20F54403E40DDCFBE07174060FB88C3AD0F5440D4FE49FB81081740A88AE4F4B80F5
440708023334C5413403CB711DD53025440EE45ADC1F45413405B0243EC5F0254402A2BE45B9D551340E972ECF56B025440E19AB60146561340601910FA77025440341013B3EE5613405D05B0F883
0254409216E86F97571340A445CEF18F02544035F622620F4D164000A4AAA1F496534001CD87CBE9471740E09A04546C61534098744DF596E51B401717474F523953401D78337C0D951E4010D9944
F3C115340AD89CA6A16862140CFC2E28DC3DB5240C5842E31561E2440A31AB9E793985240BD8C5BE4C64526406D4676A905705240A55424D1E75F28402385E2519739524084C31EB73D7C2A401E60
E240D11D5240D786518A3F712D40F35BED31F40E52403C8BF8C96E62304026AE5FD5A7F15140A381208F97D531405E60389EC4E1514086BD630860DE324036012694EDDF5140AD741D40E17C3340E
00EA98AC6DE5140E9339DDE5C7D334057D066D3D6DE5140F428BE555EE63340D2983401FADD51406269CD198B4F3440B9FC8ECE16DD5140BC38DFDB0B503440CA8056C85BDD514085A28D108C5034
40E0F9A841A1DD514003F100B50B51344028F11A3BE7DD5140824556C68A513440364940B52DDE514052B27C71748834406197CA83AEF851402902454461C434405BB0871D3D30524058A819DA7FC
A3440C6EEBDFC22685240D7C07A36B7CA34407CC17F7F32685240F442A351EECA34402D04B1E84168524022BBE72B25CB344026AB843851685240FB4E9CC55BCB3440B8302D6F606852406DBD141F
92CB34408E96DC8C6F685240E45EA438C8CB3440FE66C4917E68524082259E12FECB344054B6157E8D685240419D54AD33CC3440182401529C68524030EC190969CC344052DCB60DAB685240ADD23
F269ECC3440CA9866B1B968524092AB1705D3CC344044A23F3DC8685240756CF2A507CD3440B8D170B1D6685240406C9864EAFA3340D89D889DF98452403FAD44C337FB334042381684FE84524089
B70D3485FB33403B021A7503855240EAD919B7D2FB3340E43D9E70088552406A7C8F4C20FC3340EA46AD760D855240772095F46DFC3340AD92518712855240146151AFBBFC334075B095A21785524
015F3EA7C09FD33409A4984C81C855240270BB5E1518C334075A7F23A044E5240'::geometry))
-> Hash (cost=43457.32..43457.32 rows=82682 width=55)
(actual time=255.707..255.707 rows=82443 loops=1)
-> Seq Scan on metadata_demo m
(cost=0.00..43457.32 rows=82682 width=55) (actual time=0.013..230.904
rows=82443 loops=1)
Filter: (doc_category = 'Naxalism'::bpchar)
-> Index Scan using idx_crawled_id on page_content_demo p
(cost=0.00..9.24 rows=1 width=1353) (actual time=4.822..4.946 rows=1
loops=4129)
Index Cond: (p.crawled_page_id = l.source_id)
Total runtime: 21379.870 ms
(14 rows)
Yesterday after some Performance tuning ( shared-buffers=1GB,effective
cache-size=2Gb, work mem=64MB, maintenance_work_mem=256MB) and creating
indexes as :
CREATE INDEX idx1_source_id_l2
ON l1 USING btree(source_id,lat,lon);
CREATE INDEX idx_doc_id_m1
ON m1 USING btree(doc_id,doc_category);
CREATE INDEX idx_crawled_id_p1
ON p1
USING btree
(crawled_page_id,heading,category,crawled_page_url);
my Total runtime := Total runtime: 704.383 ms
And if run the same explain analyze command again ,Total runtime: 696.856 ms
What is the reason that first time it takes so much time and I know
second time , Postgres uses cache .
Is it possible to make it run faster at the first time too. Please let
me know.
Thanks & best Regards,
Adarsh Sharma
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
