Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B04872E003B for ; Tue, 12 Feb 2008 17:36:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 71570-02 for ; Tue, 12 Feb 2008 17:35:58 -0400 (AST) Received: from mail1.livedatagroup.com (mail1.livedatagroup.com [216.154.205.166]) by postgresql.org (Postfix) with ESMTP id A467F2E0030 for ; Tue, 12 Feb 2008 17:35:52 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail1.livedatagroup.com (Postfix) with ESMTP id ED2F6109800 for ; Tue, 12 Feb 2008 16:35:46 -0500 (EST) Received: from mail1.livedatagroup.com ([192.168.3.224]) by localhost (mail1.livedatagroup.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00495-06 for ; Tue, 12 Feb 2008 16:35:40 -0500 (EST) Received: from [192.168.2.134] (gw.livedatagroup.com [205.242.255.66]) by mail1.livedatagroup.com (Postfix) with ESMTP id 9810B1097D5 for ; Tue, 12 Feb 2008 16:35:40 -0500 (EST) Message-ID: <47B2112C.5070707@livedatagroup.com> Date: Tue, 12 Feb 2008 16:35:40 -0500 From: Pallav Kalva User-Agent: Thunderbird 2.0.0.6 (X11/20070728) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Optimizing No matching record Queries Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: amavisd-new at livedatagroup.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/122 X-Sequence-Number: 28664 Hi, I am using Postgres 8.2.4, we have to regularly run some queries on some big tables to see if we have any data for a particular request. But sometimes we might not have any matching rows on a particular request as in this case, when it cant find any matching rows it pretty much scans the whole table and it takes too long to execute. As you can see from explain analyze output the response time is horrible, Is there anything I can do to improve these queries ? Tables are autovacuumed regularly. select relname,relpages,reltuples from pg_class where relname in ('listing','listingstatus','listedaddress'); relname | relpages | reltuples ---------------+----------+------------- listing | 132725 | 9.22896e+06 listingstatus | 1 | 6 listedaddress | 63459 | 8.15774e+06 (3 rows) helix_fdc=# select relname,last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname in ('listing','listedaddress'); relname | last_autovacuum | last_autoanalyze ---------------+-------------------------------+------------------------------- listing | 2008-02-12 10:57:54.690913-05 | 2008-02-12 10:57:54.690913-05 listedaddress | 2008-02-09 14:12:44.038341-05 | 2008-02-12 11:17:47.822597-05 (3 rows) Explain Analyze Output ================ explain analyze select listing0_.listingid as listingid157_, listing0_.entrydate as entrydate157_, listing0_.lastupdate as lastupdate157_, listing0_.sourcereference as sourcere4_157_, listing0_.start as start157_, listing0_.stop as stop157_, listing0_.price as price157_, listing0_.updateHashcode as updateHa8_157_, listing0_.fklistedaddressid as fklisted9_157_, listing0_.fklistingsubtypeid as fklisti10_157_, listing0_.fkbestaddressid as fkbesta11_157_, listing0_.fklistingsourceid as fklisti12_157_, listing0_.fklistingtypeid as fklisti13_157_, listing0_.fklistingstatusid as fklisti14_157_, listing0_.fkpropertytypeid as fkprope15_157_ from listing.listing listing0_, listing.listingstatus listingsta1_, listing.listedaddress listedaddr2_ where listing0_.fklistingstatusid=listingsta1_.listingstatusid and listing0_.fklistedaddressid=listedaddr2_.listedaddressid and listing0_.fklistingsourceid=5525 and listingsta1_.shortname='active' and (listedaddr2_.fkverifiedaddressid is not null) order by listing0_.entrydate desc limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..11191.64 rows=10 width=107) (actual time=2113544.437..2113544.437 rows=0 loops=1) -> Nested Loop (cost=0.00..790129.94 rows=706 width=107) (actual time=2113544.427..2113544.427 rows=0 loops=1) -> Nested Loop (cost=0.00..783015.53 rows=853 width=107) (actual time=2113544.420..2113544.420 rows=0 loops=1) -> Index Scan Backward using idx_listing_entrydate on listing listing0_ (cost=0.00..781557.28 rows=5118 width=107) (actual time=2113544.412..2113544.412 rows=0 loops=1) Filter: (fklistingsourceid = 5525) -> Index Scan using pk_listingstatus_listingstatusid on listingstatus listingsta1_ (cost=0.00..0.27 rows=1 width=4) (never executed) Index Cond: (listing0_.fklistingstatusid = listingsta1_.listingstatusid) Filter: (shortname = 'active'::text) -> Index Scan using pk_listedaddress_listedaddressid on listedaddress listedaddr2_ (cost=0.00..8.33 rows=1 width=4) (never executed) Index Cond: (listing0_.fklistedaddressid = listedaddr2_.listedaddressid) Filter: (fkverifiedaddressid IS NOT NULL) Total runtime: 2113544.580 ms (12 rows) Table Definitions ============ \d listing.listing Table "listing.listing" Column | Type | Modifiers --------------------+-----------------------------+------------------------------------------------------------------ listingid | integer | not null default nextval(('listing.listingseq'::text)::regclass) fklistingsourceid | integer | not null fklistingtypeid | integer | not null entrydate | timestamp without time zone | not null lastupdate | timestamp without time zone | not null fklistedaddressid | integer | fkbestaddressid | integer | sourcereference | text | fkpropertytypeid | integer | not null fklistingstatusid | integer | not null start | timestamp without time zone | not null stop | timestamp without time zone | _entrydate | timestamp without time zone | default ('now'::text)::timestamp(6) without time zone price | numeric(14,2) | fklistingsubtypeid | integer | updatehashcode | text | Indexes: "pk_listing_listingid" PRIMARY KEY, btree (listingid), tablespace "indexdata" "idx_listing_entrydate" btree (entrydate), tablespace "indexdata" "idx_listing_fkbestaddressid" btree (fkbestaddressid), tablespace "indexdata" "idx_listing_fklistingsourceid" btree (fklistingsourceid), tablespace "indexdata" "idx_listing_fklistingtypeid" btree (fklistingtypeid), tablespace "indexdata" "idx_listing_lastupdate" btree (lastupdate), tablespace "indexdata" "idx_listing_sourcereference" btree (sourcereference), tablespace "indexdata" "idx_listing_stop" btree (stop), tablespace "indexdata" "idx_listing_updatehashcode" btree (updatehashcode), tablespace "indexdata" Foreign-key constraints: "fk_listing_address" FOREIGN KEY (fkbestaddressid) REFERENCES listing.address(addressid) "fk_listing_listedaddress" FOREIGN KEY (fklistedaddressid) REFERENCES listing.listedaddress(listedaddressid) "fk_listing_listingsource" FOREIGN KEY (fklistingsourceid) REFERENCES listing.listingsource(listingsourceid) "fk_listing_listingstatus" FOREIGN KEY (fklistingstatusid) REFERENCES listing.listingstatus(listingstatusid) "fk_listing_listingsubtype" FOREIGN KEY (fklistingsubtypeid) REFERENCES listing.listingsubtype(listingsubtypeid) "fk_listing_listingtypes" FOREIGN KEY (fklistingtypeid) REFERENCES listing.listingtype(listingtypeid) "fk_listing_propertytype" FOREIGN KEY (fkpropertytypeid) REFERENCES listing.propertytype(propertytypeid) \d listing.listedaddress Table "listing.listedaddress" Column | Type | Modifiers ---------------------+-----------------------------+------------------------------------------------------------------------ listedaddressid | integer | not null default nextval(('listing.listedaddressseq'::text)::regclass) fkaddressid | integer | fkverifiedaddressid | integer | verifyattempt | timestamp without time zone | _entrydate | timestamp without time zone | default ('now'::text)::timestamp(6) without time zone Indexes: "pk_listedaddress_listedaddressid" PRIMARY KEY, btree (listedaddressid), tablespace "indexdata" "uk_listedaddress_fkaddressid" UNIQUE, btree (fkaddressid), tablespace "indexdata" "idx_listedaddress_fkverifiedaddressid" btree (fkverifiedaddressid), tablespace "indexdata" Foreign-key constraints: "fk_listedaddress_address" FOREIGN KEY (fkaddressid) REFERENCES listing.address(addressid) "fk_listedaddress_verifiedaddress" FOREIGN KEY (fkverifiedaddressid) REFERENCES listing.verifiedaddress(verifiedaddressid) \d listing.listingstatus Table "listing.listingstatus" Column | Type | Modifiers -----------------+-----------------------------+------------------------------------------------------------------------ listingstatusid | integer | not null default nextval(('listing.listingstatusseq'::text)::regclass) shortname | text | longname | text | _entrydate | timestamp without time zone | default ('now'::text)::timestamp(6) without time zone Indexes: "pk_listingstatus_listingstatusid" PRIMARY KEY, btree (listingstatusid), tablespace "indexdata" TIA, Pallav