From: Russell Smith <mr-russ@pws.com.au>
To: Tom Pfeifer <tpfeifer@tela.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index use and slow queries
Date: Sun, 13 Mar 2005 17:07:46 +1100
Message-ID: <200503131707.46690.mr-russ@pws.com.au> (raw)
In-Reply-To: <20050313051830.M98712@tela.com>
References: <20050313051830.M98712@tela.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au, tpfeifer@tela.com
Subject: Re: Index use and slow queries
In-Reply-To: <200503131707.46690.mr-russ@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox