[PERFORM] How to tune this query
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)
Re: [PERFORM] how to tune this query.
On 7/4/06, Luckys [EMAIL PROTECTED] wrote: Hi all, I got this query, I'm having indexes for PropertyId and Dates columns across all the tables, but still it takes ages to get me the result. What indexes would be proposed on this, or I'm helpless? I would suggest posting your table schemas and describe what you want the results to look like. After years of following this list, I regard your query as something of a classic. There simply has to be an easier way of writing it. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] how to tune this query.
Hi all, I got this query, I'm having indexes for PropertyId and Dates columns across all the tables, but still it takes ages to get me the result. What indexes would be proposed on this, or I'm helpless? FROM STG_Property a FULL OUTER JOIN STG_PropConfirmedLogs b ON (a.PropertyId = b.PropertyId AND a.p_LastModified = b.p_Modified_Date) FULL OUTER JOIN STG_PropConnectionFeesLogs c ON ((a.PropertyId = c.PropertyId AND a.p_LastModified = c.p_ChangedOn) OR (b.PropertyId = c.PropertyId AND b.p_Modified_Date = c.p_ChangedOn)) FULL OUTER JOIN STG_PropDeletedLogs d ON ((a.PropertyId = d.PropertyId AND a.p_LastModified = d.p_DeletedOn) OR (b.PropertyId = d.PropertyId AND b.p_Modified_Date = d.p_DeletedOn) OR ( c.PropertyId = d.PropertyId AND c.p_ChangedOn = d.p_DeletedOn)) FULL OUTER JOIN STG_PropFEWALogs e ON ((a.PropertyId = e.PropertyId AND a.p_LastModified = e.p_Modified_Date) OR (b.PropertyId = e.PropertyId AND b.p_Modified_Date = e.p_Modified_Date) OR (c.PropertyId = e.PropertyId AND c.p_ChangedOn = e.p_Modified_Date) OR (d.PropertyId = e.PropertyId AND d.p_DeletedOn = e.p_Modified_Date)) FULL OUTER JOIN STG_PropInSewerNetworkLogs f ON ((a.PropertyId = f.PropertyId AND a.p_LastModified = f.p_Modified_Date) OR (b.PropertyId = f.PropertyId AND b.p_Modified_Date = f.p_Modified_Date) OR (c.PropertyId = f.PropertyId AND c.p_ChangedOn = f.p_Modified_Date) OR (d.PropertyId = f.PropertyId AND d.p_DeletedOn = f.p_Modified_Date) OR (e.PropertyId = f.PropertyId AND e.p_Modified_Date = f.p_Modified_Date)) FULL OUTER JOIN STG_PropTypeLogs g ON ((a.PropertyId = g .PropertyId AND a.p_LastModified = g.p_LastModified) OR (b.PropertyId = g.PropertyId AND b.p_Modified_Date = g.p_LastModified) OR (c.PropertyId = g.PropertyId AND c.p_ChangedOn = g.p_LastModified) OR (d.PropertyId = g.PropertyId AND d.p_DeletedOn = g.p_LastModified) OR (e.PropertyId = g.PropertyId AND e.p_Modified_Date = g.p_LastModified) OR (f.PropertyId = g.PropertyId AND f.p_Modified_Date = g.p_LastModified)) -- Luckys
Re: [PERFORM] how to tune this query.
I don't think indexes are going to help you here - with the FULL OUTER JOINs, the query will have to look at and include each row from each table you query from anyway, so it's going to choose sequential scans. In addition, some of the lower join conditions are going to take forever. What's is your goal? The volume of data that I imagine this query would produce can't possibly be useful. I'm guessing at the very least you'll want to LEFT OUTER JOIN everything back against STG_Property, and leave the other join conditions out of each ON statement. Luckys wrote: Hi all, I got this query, I'm having indexes for PropertyId and Dates columns across all the tables, but still it takes ages to get me the result. What indexes would be proposed on this, or I'm helpless? FROM STG_Property a FULL OUTER JOIN STG_PropConfirmedLogs b ON (a.PropertyId = b.PropertyId AND a.p_LastModified = b.p_Modified_Date) FULL OUTER JOIN STG_PropConnectionFeesLogs c ON ((a.PropertyId = c.PropertyId AND a.p_LastModified = c.p_ChangedOn) OR (b.PropertyId = c.PropertyId AND b.p_Modified_Date = c.p_ChangedOn)) FULL OUTER JOIN STG_PropDeletedLogs d ON ((a.PropertyId = d.PropertyId AND a.p_LastModified = d.p_DeletedOn) OR (b.PropertyId = d.PropertyId AND b.p_Modified_Date = d.p_DeletedOn) OR ( c.PropertyId = d.PropertyId AND c.p_ChangedOn = d.p_DeletedOn)) FULL OUTER JOIN STG_PropFEWALogs e ON ((a.PropertyId = e.PropertyId AND a.p_LastModified = e.p_Modified_Date) OR (b.PropertyId = e.PropertyId AND b.p_Modified_Date = e.p_Modified_Date) OR (c.PropertyId = e.PropertyId AND c.p_ChangedOn = e.p_Modified_Date) OR (d.PropertyId = e.PropertyId AND d.p_DeletedOn = e.p_Modified_Date)) FULL OUTER JOIN STG_PropInSewerNetworkLogs f ON ((a.PropertyId = f.PropertyId AND a.p_LastModified = f.p_Modified_Date) OR (b.PropertyId = f.PropertyId AND b.p_Modified_Date = f.p_Modified_Date) OR (c.PropertyId = f.PropertyId AND c.p_ChangedOn = f.p_Modified_Date) OR (d.PropertyId = f.PropertyId AND d.p_DeletedOn = f.p_Modified_Date) OR (e.PropertyId = f.PropertyId AND e.p_Modified_Date = f.p_Modified_Date)) FULL OUTER JOIN STG_PropTypeLogs g ON ((a.PropertyId = g .PropertyId AND a.p_LastModified = g.p_LastModified) OR (b.PropertyId = g.PropertyId AND b.p_Modified_Date = g.p_LastModified) OR (c.PropertyId = g.PropertyId AND c.p_ChangedOn = g.p_LastModified) OR (d.PropertyId = g.PropertyId AND d.p_DeletedOn = g.p_LastModified) OR (e.PropertyId = g.PropertyId AND e.p_Modified_Date = g.p_LastModified) OR (f.PropertyId = g.PropertyId AND f.p_Modified_Date = g.p_LastModified)) -- Luckys -- Nolan Cafferky Software Developer IT Department RBS Interactive [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
