Skip site navigation (1) Skip section navigation (2)

Re: Speed Question

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Noah Silverman <noah(at)allresearch(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Speed Question
Date: 2002-12-20 23:23:59
Message-ID: Pine.LNX.4.33.0212201618130.9060-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 20 Dec 2002, Noah Silverman wrote:

> Issue #1:  Speed of inserts is relatively slow.  100000 inserts is 
> taking
> roughly 10 minutes.  This isn't EVIL, but mysql appears to be about
> ten times faster here.  Is there something we could do to the indexes
> differently?  Disable transactions?  Is there a more "raw" insert, which
> may not set off triggers?

Are you doing these in a transaction?  If not, then try adding a 
begin;end; pair around your inserts.  i.e.

begin;
insert 100000 rows
end;

that should help.


Reading the rest of your message, it appears there are two issues here.  
One is you might get some help from a multi-column index.

Further, have you run analyze on your database?

Have you read the administrative docs yet?  There's lots more good stuff 
in there too.  These are the basics.

The other issue is the assumption that indexes are ALWAYS faster, which 
they aren't.  If the query planner thinks it's gonna grab some significant 
portion of a table, it will just grab the whole thing instead of using an 
index, which makes a certain amount of sense.  To reduce the likelihood of 
the planner picking a sequential scan, change random_page_cost from the 
default 4 to something lower.  A 1 means that the cost of grabbing a page 
randomly is the same as grabbing it sequentially, which shouldn't be 
possible, but is, if the data is all in memory.

Next, use EXPLAIN ANALYZE to get an output of both what the query planner 
THOUGHT it was going to do, and what the query actually did, in terms of 
time to execute.

Let us know how it all turns out.


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2002-12-20 23:50:25
Subject: Re: Speed Question
Previous:From: Noah SilvermanDate: 2002-12-20 22:57:28
Subject: Speed Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group