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
