On Thu, Feb 23, 2012 at 11:07 AM, Andy Colson <a...@squeakycode.net> wrote:
> That depends on if you have triggers that are doing selects. But in > general you are correct, analyze wont help inserts. > > I do have some, actually. I have a couple trigger functions like: CREATE OR REPLACE FUNCTION locations_quiet_unique_violation() RETURNS trigger AS $BODY$ BEGIN IF EXISTS (SELECT 1 FROM public.locations WHERE geohash = NEW.geohash) THEN RETURN NULL; ELSE RETURN NEW; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; that are triggered thusly: CREATE TRIGGER locations_check_unique_violation BEFORE INSERT ON locations FOR EACH ROW EXECUTE PROCEDURE locations_quiet_unique_violation(); I left auto-vacuum enabled for those tables. checkpoint_segments can help insert speed, what do you have that set to? > > 40. Checking http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serverit > looks like setting that as high as 256 would not necessarily be unreasonable. What do you think? > Also how you insert can make things faster too. (insert vs prepared vs > COPY) > > I'm doing this all with INSERT. Is COPY that much faster? I don't know anything about prepared. > Also, if you have too many indexes on a table that can cause things to > slow down. > > Yeah, got that. I removed a bunch. I'd rather not remove what's left unless I have to. > Your IO layer needs to be fast too. Have you watched vmstat and iostat? > > I don't know if I have access to vmstat and iostat. Heroku is hosting this for me on AWS. > Have you read up on synchronous_commit? > > Only a tiny bit. A couple people suggested disabling it since my database is being hosted on AWS so I did that. It seems a bit risky but perhaps worth it.