Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1D6FC1337B39 for ; Mon, 28 Feb 2011 03:18:39 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74575-10 for ; Mon, 28 Feb 2011 07:18:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp180.dfw.emailsrvr.com (smtp180.dfw.emailsrvr.com [67.192.241.180]) by mail.postgresql.org (Postfix) with ESMTP id 02E031337B2F for ; Mon, 28 Feb 2011 03:18:29 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp18.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 0786A2682ED; Mon, 28 Feb 2011 02:18:29 -0500 (EST) X-Virus-Scanned: OK Received: by smtp18.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 1BC942682EB for ; Mon, 28 Feb 2011 02:18:27 -0500 (EST) Message-ID: <4D6B4D5C.6000907@orkash.com> Date: Mon, 28 Feb 2011 12:53:08 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Is Query need to be optimized Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.6 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201102/520 X-Sequence-Number: 42658 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