bigint indices with inequalities? - Mailing list pgsql-general

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)