Thread
-
My indexes aren't being used (according to EXPLAIN)
erik@norvelle.net — 2003-12-01T13:40:30Z
Greetings: Apologies if this question has already been answered, but I was unable to locate a prior answer in the archives... I have a table with approximately 10 million records, called "indethom", and with an INTEGER column called "clavis" which is set up as a primary key. When I try to perform a select on the table, restricting the result to only the first 100 records, PostgreSQL performs a sequence scan, rather than an index scan (as shown by using EXPLAIN). Needless to say the sequence scan takes forever. Is there some way to get PostgreSQL to use my wonderful indexes? Have I somehow built the indexes incorrectly or something? Here's the description of the table: ====================== PSQL Output Snip ========================= it=> \d indethom Table "public.indethom" Column | Type | Modifiers ---------------+-----------------------+----------- numeoper | smallint | not null nomeoper | character(3) | not null ... (numerous columns skipped) ... verbum | character varying(22) | not null poslinop | integer | not null posverli | smallint | not null posverop | integer | not null clavis | integer | not null articref | integer | sectref | integer | query_counter | integer | Indexes: indethom_pkey primary key btree (clavis), indethom_articulus_ndx btree (nomeoper, refere1a, refere1b, refere2a, refere2b, refere3a, refere3b), indethom_sectio_ndx btree (nomeoper, refere1a, refere1b, refere2a, refere2b, refere3a, refere3b, refere4a, refere4b), it_clavis_ndx btree (clavis), verbum_ndx btree (verbum) it=> explain select * from indethom where clavis < 25; QUERY PLAN ---------------------------------------------------------------------- Seq Scan on indethom (cost=0.00..1336932.65 rows=3543991 width=236) Filter: (clavis < 25) (2 rows) ================== End Snip ===================== Feel free to point me to any FAQ or previous message that already answers this question. Thanks in advance! -Erik Norvelle -
Re: My indexes aren't being used (according to EXPLAIN)
Andrew Sullivan <andrew@libertyrms.info> — 2003-12-01T14:04:10Z
On Mon, Dec 01, 2003 at 02:40:30PM +0100, Erik Norvelle wrote: > > it=> explain select * from indethom where clavis < 25; What's the percentage of the table where clavis < 25? Have you ANALYSEd recently? What does the pg_stats view tell you about this table? > Feel free to point me to any FAQ or previous message that already > answers this question. Thanks in advance! This is a pretty common sort of problem. See the archives of this list for several fairly recent discussions of these sorts of problems. A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110 -
Re: My indexes aren't being used (according to EXPLAIN)
erik@norvelle.net — 2003-12-01T15:11:11Z
The ANALYSE did the trick... Thanks! Will also read through the archives... -Erik On lunes, dici 1, 2003, at 15:04 Europe/Madrid, Andrew Sullivan wrote: > On Mon, Dec 01, 2003 at 02:40:30PM +0100, Erik Norvelle wrote: >> >> it=> explain select * from indethom where clavis < 25; > > What's the percentage of the table where clavis < 25? Have you > ANALYSEd recently? What does the pg_stats view tell you about this > table? > >> Feel free to point me to any FAQ or previous message that already >> answers this question. Thanks in advance! > > This is a pretty common sort of problem. See the archives of this > list for several fairly recent discussions of these sorts of > problems. > > A > > -- > ---- > Andrew Sullivan 204-4141 Yonge Street > Afilias Canada Toronto, Ontario Canada > <andrew@libertyrms.info> M2P 2A8 > +1 416 646 3304 x110 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >