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?