Very slow left outer join - Mailing list pgsql-performance

Hi All,

I have a very slow left outer join that speeds up by more then 1000
times when I turn set enable_seqscan=off.  This is not the query I
actually do in my application, but is a simplified one that singles out
the part that is really slow.  All of the columns involved in the query
have indexes on them, but unless I set enable_seqscan=off the planner is
doing a sequential scan instead of using the indexes.  I'm hoping there
is something simple I am doing wrong that someone can point out to me.
I am using version 8.1.5.

So here is the explain analyze output first with enable_seqscan=on, and
the second with enable_seqscan=off:

mdsdb=# explain analyze select backupobjects.record_id from
backupobjects left outer join backup_location using(record_id) where
backup_id = 1071;

QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
-----
 Hash Join  (cost=361299.50..1054312.92 rows=34805 width=8) (actual
time=1446.861..368723.597 rows=2789 loops=1)
   Hash Cond: ("outer".record_id = "inner".record_id)
   ->  Seq Scan on backupobjects  (cost=0.00..429929.79 rows=13136779
width=8) (actual time=5.165..359168.216 rows=13136779 loops=1)
   ->  Hash  (cost=360207.21..360207.21 rows=436915 width=8) (actual
time=820.979..820.979 rows=2789 loops=1)
         ->  Bitmap Heap Scan on backup_location
(cost=3831.20..360207.21 rows=436915 width=8) (actual
time=797.463..818.269 rows=2789 loops=1)
               Recheck Cond: (backup_id = 1071)
               ->  Bitmap Index Scan on backup_location_bid
(cost=0.00..3831.20 rows=436915 width=0) (actual time=59.592..59.592
rows=2789 loops=1)
                     Index Cond: (backup_id = 1071)
 Total runtime: 368725.122 ms
(9 rows)

mdsdb=# explain analyze select backupobjects.record_id from
backupobjects left outer join backup_location using(record_id) where
backup_id = 1071;
                                                                  QUERY
PLAN

------------------------------------------------------------------------
-----------------------------------------------------------------------
 Nested Loop  (cost=3833.21..1682311.27 rows=34805 width=8) (actual
time=103.132..201.808 rows=2789 loops=1)
   ->  Bitmap Heap Scan on backup_location  (cost=3831.20..360207.21
rows=436915 width=8) (actual time=94.375..97.688 rows=2789 loops=1)
         Recheck Cond: (backup_id = 1071)
         ->  Bitmap Index Scan on backup_location_bid
(cost=0.00..3831.20 rows=436915 width=0) (actual time=84.239..84.239
rows=2789 loops=1)
               Index Cond: (backup_id = 1071)
   ->  Bitmap Heap Scan on backupobjects  (cost=2.00..3.01 rows=1
width=8) (actual time=0.033..0.034 rows=1 loops=2789)
         Recheck Cond: (backupobjects.record_id = "outer".record_id)
         ->  Bitmap Index Scan on backupobjects_pkey  (cost=0.00..2.00
rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=2789)
               Index Cond: (backupobjects.record_id = "outer".record_id)
 Total runtime: 203.378 ms
(10 rows)

Here are the two tables in the query:

mdsdb=# \d backup_location
 Table "public.backup_location"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 record_id | bigint  | not null
 backup_id | integer | not null
Indexes:
    "backup_location_pkey" PRIMARY KEY, btree (record_id, backup_id)
    "backup_location_bid" btree (backup_id)
    "backup_location_rid" btree (record_id)
Foreign-key constraints:
    "backup_location_bfk" FOREIGN KEY (backup_id) REFERENCES
backups(backup_id) ON DELETE CASCADE

mdsdb=# \d backupobjects
               Table "public.backupobjects"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 record_id      | bigint                      | not null
 dir_record_id  | integer                     |
 name           | text                        |
 extension      | character varying(64)       |
 hash           | character(40)               |
 mtime          | timestamp without time zone |
 size           | bigint                      |
 user_id        | integer                     |
 group_id       | integer                     |
 meta_data_hash | character(40)               |
Indexes:
    "backupobjects_pkey" PRIMARY KEY, btree (record_id)
    "backupobjects_meta_data_hash_key" UNIQUE, btree (meta_data_hash)
    "backupobjects_extension" btree (extension)
    "backupobjects_hash" btree (hash)
    "backupobjects_mtime" btree (mtime)
    "backupobjects_size" btree (size)

Thanks,
Ed