Re: indexes are fucked
От | Madison Kelly |
---|---|
Тема | Re: indexes are fucked |
Дата | |
Msg-id | 42EFB203.1080000@alteeve.com обсуждение исходный текст |
Ответ на | indexes are fucked (Dr NoName <spamacct11@yahoo.com>) |
Список | pgsql-general |
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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
В списке pgsql-general по дате отправления: