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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Mascari | 2004-04-29 13:50:34 | Re: Postgre and Web Request |
Previous Message | Howard, Steven (US - Tulsa) | 2004-04-29 13:34:06 | Performance problem with correlated sub-query |