Re: indexes are farked

siam_production=> explain analyze select * from render
where person_id = 432;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on render (cost=0.00..39014.72 rows=27833
width=1493) (actual time=7.11..743.55 rows=5261
loops=1)
Filter: (person_id = 432)
Total runtime: 747.42 msec
(3 rows)

thanks,

Eugene

--- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:

> 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>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings
>

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs