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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
