pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index use and slow queries
3+ messages / 3 participants
[nested] [flat]

* Index use and slow queries
@ 2005-03-13 05:40 Tom Pfeifer <tpfeifer@tela.com>
  2005-03-13 06:07 ` Re: Index use and slow queries Russell Smith <mr-russ@pws.com.au>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Pfeifer @ 2005-03-13 05:40 UTC (permalink / raw)
  To: pgsql-performance

<HTML>
<HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META content="OPENWEBMAIL" name=GENERATOR>
</HEAD>
<BODY bgColor=#ffffff>

<p><font size="2">Hello,</font></p>

<p><font size="2">My version of Postgresql is 7.4.3.
<br />I have a simple table with 2 indexes:
<br />                             Table &quot;public.tst&quot;
<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 time zone | default now()
<br />Indexes:
<br />    &quot;tst_idx&quot; unique, btree (mmd5, active)
<br />    &quot;tst_tst_id_key&quot; 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>

<p><font size="2">I get really fast response times when using the 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 Scan using 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 really slow 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>

<p><font size="2">Why is the second select statement so slow, it should be using the &quot;tst_tst_id_key&quot; 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>

<p><font size="2">When I turned off,  maach=# SET ENABLE_SEQSCAN TO OFF;
<br />The slow select statement gets even slower.
<br />maach=# explain select * from tst where tst_id = 639246;
<br />                             QUERY PLAN
<br />--------------------------------------------------------------------
<br /> Seq Scan on tst  (cost=100000000.00..100023370.00 rows=1 width=57)
<br />   Filter: (tst_id = 639246)
<br />(2 rows)
<br /></font></p>

<p><font size="2">Why do I have to use 2 columns to create a fast/efficient index?  I want to get the single column index to be the fastest index for my select statements.  How do I accomplish this.</font></p>

<p><font size="2">Thanks,
<br />Tom</font></p>
</p>
</p>

</BODY>
</HTML>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Index use and slow queries
  2005-03-13 05:40 Index use and slow queries Tom Pfeifer <tpfeifer@tela.com>
@ 2005-03-13 06:07 ` Russell Smith <mr-russ@pws.com.au>
  2005-03-13 06:26   ` Re: Index use and slow queries Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Russell Smith @ 2005-03-13 06:07 UTC (permalink / raw)
  To: Tom Pfeifer <tpfeifer@tela.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Index use and slow queries
  2005-03-13 05:40 Index use and slow queries Tom Pfeifer <tpfeifer@tela.com>
  2005-03-13 06:07 ` Re: Index use and slow queries Russell Smith <mr-russ@pws.com.au>
@ 2005-03-13 06:26   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2005-03-13 06:26 UTC (permalink / raw)
  To: Russell Smith <mr-russ@pws.com.au>; +Cc: Tom Pfeifer <tpfeifer@tela.com>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-03-13 06:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-13 05:40 Index use and slow queries Tom Pfeifer <tpfeifer@tela.com>
2005-03-13 06:07 ` Russell Smith <mr-russ@pws.com.au>
2005-03-13 06:26   ` Tom Lane <tgl@sss.pgh.pa.us>

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