Re: indexes are farked - Mailing list pgsql-general

On Tue, 2005-08-02 at 12:04, Dr NoName wrote:
> Hi all,
>
> I got another problem with postgres. This time it
> refuses to use the indexes. Check this out:
>
>
> siam_production=> \d render
>                                             Table
> "public.render"
>         Column        |            Type             |
>                      Modifiers
> ----------------------+-----------------------------+--------------------------------------------------------
>  id                   | integer                     |
> not null default nextval('public.render_id_seq'::text)
>  shot_id              | integer                     |
> not null
>  process              | character(3)                |
> not null
>  person_id            | integer                     |
> not null
>  session_xml          | text                        |
> not null
>  guts_snapshot_id     | integer                     |
>  layer                | text                        |
>  render_path          | text                        |
> not null
>  frames               | text                        |
> not null
>  shot_index           | integer                     |
> not null
>  timestamp            | timestamp without time zone |
> not null default now()
>  layer_render_version | integer                     |
>  num_frames           | integer                     |
>  mean_render_time     | integer                     |
>  stdev_render_time    | integer                     |
>  min_render_time      | integer                     |
>  max_render_time      | integer                     |
>  failed_frames        | text                        |
>  swapped_frames       | text                        |
>  killed_frames        | text                        |
>  status               | character varying(10)       |
>  render_settings      | text                        |
>  explicit_guts_log    | text                        |
>  completed_frames     | integer                     |
>  priority             | character varying(3)        |
>  render_host          | character varying(10)       |
> Indexes: render_pkey primary key btree (id),
>          render_person_id_idx btree (person_id),
>          render_shot_id_idx btree (shot_id)
> Foreign Key constraints: $3 FOREIGN KEY
> (guts_snapshot_id) REFERENCES shot_snapshot(id) ON
> UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY
> DEFERRED,
>                          $2 FOREIGN KEY (process)
> REFERENCES process_enum(code) ON UPDATE CASCADE ON
> DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
>                          $1 FOREIGN KEY (shot_id)
> REFERENCES shot(id) ON UPDATE CASCADE ON DELETE
> RESTRICT DEFERRABLE INITIALLY DEFERRED
>
> siam_production=> explain SELECT render.* FROM render
> WHERE person_id = 432;
>                            QUERY PLAN
> -----------------------------------------------------------------
>  Seq Scan on render  (cost=0.00..39014.72 rows=27833
> width=1493)
>    Filter: (person_id = 432)
> (2 rows)
>
> siam_production=>
>
>
> As you can see, there is an index on render.person_id,
> but postgres is using sequential scan. I have tried
> *repeatedly* to reindex, analyze, drop & create index,
> vacuum, etc. to no avail. What is wrong? I need this
> fixed ASAP. It's killing the performance.
>
> btw, the same thing would happen to
> render_shot_id_idx, but after repeatedly doing
> reindex, alanyze, vacuum, drop & create index, etc. it
> suddenly started to work.

1:  Please refrain from the f word.  There are some kids in schools (not
university) reading this list.  there's really no need.

Please post the output of

explain analyze <yourqueryhere>