Re: indexes are farked - Mailing list pgsql-general

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@g2switchworks.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