| From: | "Tom Pfeifer" <tpfeifer(at)tela(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Index use and slow queries |
| Date: | 2005-03-13 05:40:47 |
| Message-ID: | 20050313051830.M98712@tela.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | 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 "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 time zone | 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>
<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 "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>
<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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Russell Smith | 2005-03-13 06:07:46 | Re: Index use and slow queries |
| Previous Message | Josh Berkus | 2005-03-12 22:05:20 | Re: One tuple per transaction |