Обсуждение: Index use and slow queries

<p><font size="2">Hello,</font><p><font size="2">My version of Postgresql is 7.4.3. <br />I have a simple table with 2
indexes:<br />                             Table "public.tst" <br /> Column |            Type            
|             Modifiers <br />--------+-----------------------------+------------------------------------- <br
/> tst_id| bigint                      | default nextval('tst_id_seq'::text) <br /> mmd5   | character
varying(32)      | not null <br /> active | character(1)                | not null <br /> lud    | timestamp without
timezone | default now() <br />Indexes: <br />    "tst_idx" unique, btree (mmd5, active) <br />    "tst_tst_id_key"
unique,btree (tst_id) <br /></font><p><font size="2">There are exactly 1,000,000 (one million) rows in the table
(tst). There are no NULLS, empty columns in any row.</font><p><font size="2">I get really fast response
times when usingthe following select statement (Less than 1 second). <br /></font><font size="2">maach=# explain select
*from tst where mmd5 = '71e1c18cbc708a0bf28fe106e03256c7' and active = 'A'; <br
/>                                             QUERY PLAN <br
/>------------------------------------------------------------------------------------------------------<br /> Index
Scanusing tst_idx on tst  (cost=0.00..6.02 rows=1 width=57) <br />   Index Cond: (((mmd5)::text =
'71e1c18cbc708a0bf28fe106e03256c7'::text)AND (active = 'A'::bpchar)) <br />(2 rows) <br /></font><p><font size="2">I
get reallyslow repoonse times when using the following select statement (About 20 seconds). <br />maach=# explain
select* from tst where tst_id = 639246; <br />                       QUERY PLAN <br
/>--------------------------------------------------------<br /> Seq Scan on tst  (cost=0.00..23370.00 rows=1 width=57)
<br/>   Filter: (tst_id = 639246) <br />(2 rows) <br /></font><p><font size="2">Why is the second select statement so
slow,it should be using the "tst_tst_id_key" unique, btree (tst_id) index, but instead EXPLAIN says it's using a Seq
Scan. If it was using the index, this select statement should be as fast if not faster than the above select
statement.</font><p><fontsize="2">When I turned off,  maach=# SET ENABLE_SEQSCAN TO OFF; <br />The slow select
statementgets even slower. <br />maach=# explain select * from tst where tst_id = 639246; <br
/>                            QUERY PLAN <br />-------------------------------------------------------------------- <br
/> SeqScan on tst  (cost=100000000.00..100023370.00 rows=1 width=57) <br />   Filter: (tst_id = 639246) <br />(2 rows)
<br/></font><p><font size="2">Why do I have to use 2 columns to create a fast/efficient index?  I want to get the
singlecolumn index to be the fastest index for my select statements.  How do I accomplish this.</font><p><font
size="2">Thanks,<br />Tom</font> 

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

Russell Smith <mr-russ@pws.com.au> writes:
> On Sun, 13 Mar 2005 04:40 pm, Tom Pfeifer wrote:
>> 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.

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

... or you compare to a value large enough to be int8 naturally, eg

> explain select * from tst where tst_id = 123456639246;

The issue here is that (a) 639246 is naturally typed as int4, and
(b) before 8.0 we couldn't use cross-type comparisons such as int8 = int4
with an index.

You can find a whole lot of angst about this issue and related ones
if you care to review the last six or eight years of the pgsql-hackers
archives.  It was only recently that we found a way to support
cross-type index operations without breaking the fundamental
type-extensibility features of Postgres.  (In hindsight, we spent way
too much time fixated on the notion that we needed to find a way to
implicitly convert the non-indexed value to match the indexed column's
type, rather than biting the bullet and supporting cross-type operations
directly with indexes.  Oh well, hindsight is always 20/20.)

            regards, tom lane