Is Query need to be optimized - Mailing list pgsql-performance

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