Skip site navigation (1) Skip section navigation (2)

Unable to use index?

From: Edmund Dengler <edmundd(at)eSentire(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unable to use index?
Date: 2004-04-29 13:48:10
Message-ID: Pine.BSO.4.58.0404290940420.21603@cyclops4.esentire.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi folks!

A query I am running does not seem to use indexes that are available
(running version 7.4.2). I have the following table:

=> \d replicated
                                    Table "public.replicated"
     Column      |           Type           |
Modifiers
-----------------+--------------------------+-----------------------------------------------------
 rep_id          | bigint                   | not null default nextval('replicated_id_seq'::text)
 rep_component   | character varying(100)   |
 rep_key1        | integer                  |
 rep_key2        | bigint                   |
 rep_key3        | smallint                 |
 rep_replicated  | timestamp with time zone |
 rep_remotekey1  | integer                  |
 rep_remotekey2  | bigint                   |
 rep_remotekey3  | smallint                 |
 rep_key2b       | bigint                   |
 rep_remotekey2b | bigint                   |
 rep_key4        | text                     |
Indexes:
    "replicated_pkey" primary key, btree (rep_id)
    "replicate_key1_idx" btree (rep_key1, rep_key2, rep_key3)
    "replicated_item2_idx" btree (rep_component, rep_key2, rep_key3)
    "replicated_item_idx" btree (rep_component, rep_key1, rep_key2, rep_key3)
    "replicated_key2_idx" btree (rep_key2, rep_key3)
    "replicated_key4_idx" btree (rep_key4)

=> analyze verbose replicated;
INFO:  analyzing "public.replicated"
INFO:  "replicated": 362140 pages, 30000 rows sampled, 45953418 estimated
total rows
ANALYZE

The following does not use an index, even though two are available for the
specific selection of rep_component.

=> explain analyze select * from replicated where rep_component = 'ps_probe' limit 1;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.23 rows=1 width=101) (actual time=34401.857..34401.859 rows=1 loops=1)
   ->  Seq Scan on replicated  (cost=0.00..936557.70 rows=4114363 width=101) (actual time=34401.849..34401.849 rows=1 loops=1)
         Filter: ((rep_component)::text = 'ps_probe'::text)
 Total runtime: 34401.925 ms
(4 rows)

Yet, if I do the following, an index will be used, and it runs much
faster (even when I swapped the order of the execution).

=> explain analyze select * from replicated where rep_component = 'ps_probe' order by rep_component limit 1;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1.66 rows=1 width=101) (actual time=51.163..51.165 rows=1 loops=1)
   ->  Index Scan using replicated_item2_idx on replicated  (cost=0.00..6838123.76 rows=4114363 width=101) (actual time=51.157..51.157 rows=1 loops=1)
         Index Cond: ((rep_component)::text = 'ps_probe'::text)
 Total runtime: 51.265 ms
(4 rows)

Any reason why the index is not chosen? Maybe I need to up the number of
rows sampled for statistics?

Regards!
Ed

Responses

pgsql-general by date

Next:From: Mike MascariDate: 2004-04-29 13:50:34
Subject: Re: Postgre and Web Request
Previous:From: Howard, Steven (US - Tulsa)Date: 2004-04-29 13:34:06
Subject: Performance problem with correlated sub-query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group