Greetings to all,
I use to run below query on my Postgres Database Server very often :
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((26.493618940784085
94.73526463903742,26.493618940784085 94.73526463903742,26.49414347324995
94.73609294031571,25.27305797085655 91.2111565730387,22.577266399435437
91.25956595906088,21.786005217742066 93.8817223698167,24.890143541531135
95.16269696276306,24.89070526076922 95.16324228285777,24.89070526076922
95.16324228285777,26.493618940784085 94.73526463903742))',4326)) and
m.doc_category='Terrorism' order by p.dt_stamp desc;
I think I need to optimized above query for fast execution as I can. Any
suggestions are always welcome :
Explain output :
Sort (cost=160385.28..160386.32 rows=418 width=1316)
Sort Key: p.dt_stamp
-> Hash Join (cost=85558.37..160367.08 rows=418 width=1316)
Hash Cond: (p.crawled_page_id = l.source_id)
-> Seq Scan on page_content_demo p (cost=0.00..73344.20
rows=389420 width=1251)
-> Hash (cost=85553.92..85553.92 rows=356 width=73)
-> Hash Join (cost=37301.92..85553.92 rows=356 width=73)
Hash Cond: (l.source_id = m.doc_id)
-> Seq Scan on loc_context_demo l
(cost=0.00..48108.71 rows=356 width=18)
Filter: ((geom &&
'0103000020E6100000010000000A000000935A97CF5D7E3A408BA46A930EAF5740935A97CF5D7E3A408BA46A930EAF5740F023C92F807E3
A403D5E90251CAF5740B2BD8E20E745394059E2DB9683CD5640A6A712BBC793364091548ABA9CD0564002B050A337C93540EBA0A9236E785740319F7772E0E33840758E85A069CA574003618D4205
E43840B48CC28F72CA574003618D4205E43840B48CC28F72CA5740935A97CF5D7E3A408BA46A930EAF5740'::geometry)
AND _st_within(geom, '0103000020E6100000010000000A00000093
5A97CF5D7E3A408BA46A930EAF5740935A97CF5D7E3A408BA46A930EAF5740F023C92F807E3A403D5E90251CAF5740B2BD8E20E745394059E2DB9683CD5640A6A712BBC793364091548ABA9CD0564
002B050A337C93540EBA0A9236E785740319F7772E0E33840758E85A069CA574003618D4205E43840B48CC28F72CA574003618D4205E43840B48CC28F72CA5740935A97CF5D7E3A408BA46A930EAF
5740'::geometry))
-> Hash (cost=37186.32..37186.32 rows=9248 width=55)
-> Seq Scan on metadata_demo m
(cost=0.00..37186.32 rows=9248 width=55)
Filter: (doc_category =
'Terrorism'::bpchar)
(13
rows)
Explain Ananlyze Output
:-
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------
Sort (cost=160385.28..160386.32 rows=418 width=1316) (actual
time=1210.025..1210.041 rows=21 loops=1)
Sort Key: p.dt_stamp
Sort Method: quicksort Memory: 65kB
-> Hash Join (cost=85558.37..160367.08 rows=418 width=1316) (actual
time=619.985..1209.821 rows=21 loops=1)
Hash Cond: (p.crawled_page_id = l.source_id)
-> Seq Scan on page_content_demo p (cost=0.00..73344.20
rows=389420 width=1251) (actual time=0.006..290.829 rows=362293 loops=1)
-> Hash (cost=85553.92..85553.92 rows=356 width=73) (actual
time=507.942..507.942 rows=21 loops=1)
-> Hash Join (cost=37301.92..85553.92 rows=356
width=73) (actual time=215.384..507.903 rows=21 loops=1)
Hash Cond: (l.source_id = m.doc_id)
-> Seq Scan on loc_context_demo l
(cost=0.00..48108.71 rows=356 width=18) (actual time=0.986..316.129
rows=816 loops=1)
Filter: ((geom &&
'0103000020E6100000010000000A000000935A97CF5D7E3A408BA46A930EAF5740935A97CF5D7E3A408BA46A930EAF5740F023C92F807E3
A403D5E90251CAF5740B2BD8E20E745394059E2DB9683CD5640A6A712BBC793364091548ABA9CD0564002B050A337C93540EBA0A9236E785740319F7772E0E33840758E85A069CA574003618D4205
E43840B48CC28F72CA574003618D4205E43840B48CC28F72CA5740935A97CF5D7E3A408BA46A930EAF5740'::geometry)
AND _st_within(geom, '0103000020E6100000010000000A00000093
5A97CF5D7E3A408BA46A930EAF5740935A97CF5D7E3A408BA46A930EAF5740F023C92F807E3A403D5E90251CAF5740B2BD8E20E745394059E2DB9683CD5640A6A712BBC793364091548ABA9CD0564
002B050A337C93540EBA0A9236E785740319F7772E0E33840758E85A069CA574003618D4205E43840B48CC28F72CA574003618D4205E43840B48CC28F72CA5740935A97CF5D7E3A408BA46A930EAF
5740'::geometry))
-> Hash (cost=37186.32..37186.32 rows=9248
width=55) (actual time=190.396..190.396 rows=9016 loops=1)
-> Seq Scan on metadata_demo m
(cost=0.00..37186.32 rows=9248 width=55) (actual time=38.895..183.396
rows=9016 loops=1)
Filter: (doc_category =
'Terrorism'::bpchar)
Total runtime: 1210.112 ms
(15 rows)
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
