bigint indices with inequalities?

Well, I'm stumped. I've just read through several discussions in the
archive about how to get the planner to use an index on a bigint column,
but the tricks (casting literals to bigint, single-quoting literals) aren't
working for me. I wish to replace the Seq Scans on _dbm_pending_data and
_dbm_pending below with some sort of indexed scan.

Can anyone help me understand why this query is not using an index? Schema,
query, and explain output for 3 different attempts are below.

Thanks in advance.

Ed

CREATE TABLE _dbm_mirrorhost (
mirror_host_id SERIAL,
hostname VARCHAR NOT NULL,
port INTEGER NOT NULL DEFAULT 5432,
last_xid BIGINT NOT NULL DEFAULT 0,
last_seq_id BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY(mirror_host_id)
);
CREATE UNIQUE INDEX _dbm_mirrorhost_uidx
ON _dbm_mirrorhost (hostname,port);

CREATE TABLE _dbm_pending (
seq_id BIGSERIAL,
tablename VARCHAR NOT NULL,
op CHARACTER,
xid BIGINT NOT NULL,
commit_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (seq_id)
);
CREATE INDEX _dbm_pending_xid_idx ON _dbm_pending (xid);
CREATE INDEX _dbm_pending_seqid_idx ON _dbm_pending (seq_id);

CREATE TABLE _dbm_pending_data (
id BIGSERIAL,
seq_id BIGINT NOT NULL,
is_key BOOLEAN NOT NULL,
data VARCHAR,
PRIMARY KEY (seq_id, is_key),
FOREIGN KEY (seq_id) REFERENCES _dbm_pending (seq_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE INDEX _dbm_pending_data_seqid_idx ON _dbm_pending_data (seq_id);
CREATE INDEX _dbm_pending_data_id_idx ON _dbm_pending_data (id);

SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

SELECT COUNT(*) FROM _dbm_pending_data;
count
-------
36474
(1 row)

SELECT COUNT(*) FROM _dbm_pending;
count
-------
36474
(1 row)

SELECT COUNT(*) FROM _dbm_mirrorhost;
count
-------
1
(1 row)

VACUUM ANALYZE;
VACUUM
EXPLAIN SELECT p.xid, p.seq_id, p.tablename, p.op, pd.is_key, pd.data,
now() - p.commit_time as "age"
FROM _dbm_pending_data pd, _dbm_pending p, _dbm_mirrorhost mh
WHERE p.seq_id = pd.seq_id
AND mh.hostname = 'rowdy'
AND mh.port = '9001'
AND p.xid > mh.last_xid
AND p.seq_id > mh.last_seq_id
AND p.xid > cast(268010 AS BIGINT)
ORDER BY p.xid ASC, p.seq_id ASC, pd.id ASC
LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=2739.37..2739.39 rows=10 width=142)
-> Sort (cost=2739.37..2749.33 rows=3986 width=142)
Sort Key: p.xid, p.seq_id, pd.id
-> Hash Join (cost=1382.04..2500.98 rows=3986 width=142)
Hash Cond: ("outer".seq_id = "inner".seq_id)
-> Seq Scan on _dbm_pending_data pd (cost=0.00..886.74
rows=36474 width=80)
-> Hash (cost=1372.08..1372.08 rows=3986 width=62)
-> Nested Loop (cost=0.00..1372.08 rows=3986
width=62)
Join Filter: (("inner".xid > "outer".last_xid)
AND ("inner".seq_id > "outer".last_seq_id))
-> Seq Scan on _dbm_mirrorhost mh
(cost=0.00..1.01 rows=1 width=16)
Filter: ((hostname = 'rowdy'::character
varying) AND (port = 9001))
-> Seq Scan on _dbm_pending p
(cost=0.00..832.93 rows=35876 width=46)
Filter: (xid > 268010::bigint)
(13 rows)

EXPLAIN SELECT p.xid, p.seq_id, p.tablename, p.op, pd.is_key, pd.data,
now() - p.commit_time as "age"
FROM _dbm_pending_data pd, _dbm_pending p, _dbm_mirrorhost mh
WHERE p.seq_id = pd.seq_id
AND mh.hostname = 'rowdy'
AND mh.port = '9001'
AND p.xid > mh.last_xid
AND p.seq_id > mh.last_seq_id
AND p.xid > '268010'::BIGINT
ORDER BY p.xid ASC, p.seq_id ASC, pd.id ASC
LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=2739.37..2739.39 rows=10 width=142)
-> Sort (cost=2739.37..2749.33 rows=3986 width=142)
Sort Key: p.xid, p.seq_id, pd.id
-> Hash Join (cost=1382.04..2500.98 rows=3986 width=142)
Hash Cond: ("outer".seq_id = "inner".seq_id)
-> Seq Scan on _dbm_pending_data pd (cost=0.00..886.74
rows=36474 width=80)
-> Hash (cost=1372.08..1372.08 rows=3986 width=62)
-> Nested Loop (cost=0.00..1372.08 rows=3986
width=62)
Join Filter: (("inner".xid > "outer".last_xid)
AND ("inner".seq_id > "outer".last_seq_id))
-> Seq Scan on _dbm_mirrorhost mh
(cost=0.00..1.01 rows=1 width=16)
Filter: ((hostname = 'rowdy'::character
varying) AND (port = 9001))
-> Seq Scan on _dbm_pending p
(cost=0.00..832.93 rows=35876 width=46)
Filter: (xid > 268010::bigint)
(13 rows)

EXPLAIN SELECT p.xid, p.seq_id, p.tablename, p.op, pd.is_key, pd.data,
now() - p.commit_time as "age"
FROM _dbm_pending_data pd, _dbm_pending p, _dbm_mirrorhost mh
WHERE p.seq_id = pd.seq_id
AND mh.hostname = 'rowdy'
AND mh.port = '9001'
AND p.xid > mh.last_xid
AND p.seq_id > mh.last_seq_id
AND p.xid > '268010'
ORDER BY p.xid ASC, p.seq_id ASC, pd.id ASC
LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=2739.37..2739.39 rows=10 width=142)
-> Sort (cost=2739.37..2749.33 rows=3986 width=142)
Sort Key: p.xid, p.seq_id, pd.id
-> Hash Join (cost=1382.04..2500.98 rows=3986 width=142)
Hash Cond: ("outer".seq_id = "inner".seq_id)
-> Seq Scan on _dbm_pending_data pd (cost=0.00..886.74
rows=36474 width=80)
-> Hash (cost=1372.08..1372.08 rows=3986 width=62)
-> Nested Loop (cost=0.00..1372.08 rows=3986
width=62)
Join Filter: (("inner".xid > "outer".last_xid)
AND ("inner".seq_id > "outer".last_seq_id))
-> Seq Scan on _dbm_mirrorhost mh
(cost=0.00..1.01 rows=1 width=16)
Filter: ((hostname = 'rowdy'::character
varying) AND (port = 9001))
-> Seq Scan on _dbm_pending p
(cost=0.00..832.93 rows=35876 width=46)
Filter: (xid > 268010::bigint)
(13 rows)