Re: indexes are fucked

This is really the only thing I can think to suggest;

Have you tried 'SET enable_seqscan TO OFF;' and then tried the query
again? This happens to me now and then where an index is a lot faster
but the planner just doesn't want to use it. I've got an option in my
code to turn off 'enable_seqscan', perform the query, and turn in back
on for problem queries.

I'm still pretty new though so defer to anyone else's suggestions.

HTH

Madison

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.
>
> Eugene
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly
TLE-BU, The Linux Experience; Back Up
http://tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-