Re: Seeking help with a query that takes too long

> Does actor_case_assignment contain more columns than just the two ids?
> If yes, do these additional fields account for ca. 70 bytes per tuple?
> If not, try
> VACUUM FULL ANALYSE actor_case_assignment;

actor_case_assignment has its own primary key and a "role" field in addition
to the ids you've seen, so 70 bytes sounds reasonable. (The PK is to allow a
remote mirroring application to update these records- otherwise it would be
unnecessary.)

> 7ms per
> tuple returned looks like a lot of disk seeks are involved. Is
> clustering actor on actor_full_name_uppercase an option or would this
> slow down other queries?

Good question... I've never used clustering in PostgreSQL before, so I'm
unsure. I presume this is like clustering in Oracle where the table is
ordered to match the index? If so, I think you may be onto something because
the only other field We regularly query on is the actor_id. Actor_id has a
unique index with no clustering currently, so I don't think I'd lose a thing
by clustering on actor_full_name_uppercase.

I'll give this a try & let you know how it changes.

BTW, you are correct that caching has a big affect on the actual time
figures in this case- I'm working on my development DB, so cahced info
doesn't get trampled as quickly by other users. Is there a way to flush out
the cache in a testing situation like this in order to start from a
consistent base?

Thanks!
-Nick