Re: bigint indices with inequalities?

On Tue, 18 Mar 2003, Ed L. wrote:

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

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

> 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)

It's estimating that the xid condition is not very selective (35876 of
36474). If that's true an index scan is likely to be a loser against the
sequence scan.

Have you done an analyze recently? What does explain analyze say for this
query? If you set enable_seqscan=off and then run explain analyze, what
does that give you?