Re: indexes are farked

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>