Re: [SQL] Indices are not used by the optimizer

Hi Reiner,

normally these kind of subjects must go
to pgsql-performance@postgresql.org

What's important is in pg_class and pg_statistic tables.
Especially, you may check out histgraph bounds
in pg_stats for attribute epoche.

For a test, did you do a
# set enable_seqscan to OFF
??
On Mon, 5 May 2003, Reiner Dassing wrote:

> Hello all!
>
> On PostgreSQL V7.3.2 on TRU64 I have a table
> and applied indices for that table.
> But on a simple query the indices are not used by the optimizer.
> (An sequential scan is used which takes a lot of time)
> I have done
> VACUUM and VACUUM analyze
> but without any change to the optimizer.
>
> Can someone give me a hint what I should do to give the
> optimizer a start?
> --------------------------------------
>
> Well, let's start by the query
>
> wetter=# explain select * from wetter where epoche > '2001-01-01';
>                                 QUERY PLAN
> -------------------------------------------------------------------------
>   Seq Scan on wetter  (cost=0.00..614795.55 rows=19054156 width=16)
>     Filter: (epoche > '2001-01-01 00:00:00+00'::timestamp with time zone)
> (2 rows)
>
> wetter=#
>
>
> The table definition is as follows:
>   \d wetter
>                Table "public.wetter"
>    Column   |           Type           | Modifiers
> -----------+--------------------------+-----------
>   sensor_id | integer                  | not null
>   epoche    | timestamp with time zone | not null
>   wert      | real                     | not null
> Indexes: wetter_pkey primary key btree (sensor_id, epoche),
>           wetter_epoche_idx btree (epoche),
>           wetter_sensor_id_idx btree (sensor_id)
> Triggers: RI_ConstraintTrigger_45702811,
>            t_ins_wetter_wetterakt
>
> wetter=#
>
>
> The trigger information is as follows:
> select * from pg_trigger where tgname='RI_ConstraintTrigger_45702811';
>   tgrelid  |            tgname             | tgfoid | tgtype | tgenabled
> | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
> tginitdeferred | tgnargs | tgattr |
>     tgargs
>
----------+-------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+---------------------------------------------------------------------------------------
>   43169106 | RI_ConstraintTrigger_45702811 |   1644 |     21 | t
>   | t              | <unnamed>    |      43169098 | f            | f
>            |       6 |        |
> <unnamed>\000wetter\000sensoren_an_orten\000UNSPECIFIED\000sensor_id\000sensor_id\000
> (1 row)
>
> wetter=#
>
>
> and t_ins_wetter_wetterakt
> is a PLPGSQL Funktion which copies some information into another table
> when an insert or update is done.
>
>
>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr