Re: Index use and slow queries

On Sun, 13 Mar 2005 04:40 pm, Tom Pfeifer wrote:
> Hello,
>
>
> My version of Postgresql is 7.4.3.
> I have a simple table with 2 indexes:
>                              Table "public.tst"
>  Column |            Type             |              Modifiers
> --------+-----------------------------+-------------------------------------
>  tst_id | bigint                      | default nextval('tst_id_seq'::text)
>  mmd5   | character varying(32)       | not null
>  active | character(1)                | not null
>  lud    | timestamp without time zone | default now()
> Indexes:
>     "tst_idx" unique, btree (mmd5, active)
>     "tst_tst_id_key" unique, btree (tst_id)
>
>
>
> There are exactly 1,000,000 (one million) rows in the table (tst).  There are no NULLS, empty columns in any row.
>
>
> I get really fast response times when using the following select statement (Less than 1 second).
> maach=# explain select * from tst where mmd5 = '71e1c18cbc708a0bf28fe106e03256c7' and active = 'A';
>                                               QUERY PLAN
> ------------------------------------------------------------------------------------------------------
>  Index Scan using tst_idx on tst  (cost=0.00..6.02 rows=1 width=57)
>    Index Cond: (((mmd5)::text = '71e1c18cbc708a0bf28fe106e03256c7'::text) AND (active = 'A'::bpchar))
> (2 rows)
>
>
>
> I get really slow repoonse times when using the following select statement (About 20 seconds).
> maach=# explain select * from tst where tst_id = 639246;

Before 8.0, bigint would not use an index unless you cast it, or quote it.

eg
explain select * from tst where tst_id = 639246::int8;
explain select * from tst where tst_id = '639246';

Hope this helps.

Russell Smith