My indexes aren't being used (according to EXPLAIN) - Mailing list pgsql-performance

<fixed><fontfamily><param>Courier New</param>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

</fontfamily></fixed>
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