Search Postgresql Archives

indexes are fucked

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux