Optimizing No matching record Queries - Mailing list pgsql-performance

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