pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Holdoway <steve.holdoway@firetrust.com>
To: pgsql-admin@postgresql.org
Subject: Re: improve select performance...
Date: Sat, 19 May 2007 14:51:45 +1200
Message-ID: <20070519145145.0c16e6fc.steve.holdoway@firetrust.com> (raw)
In-Reply-To: <20070516110810.ba3554fe.steve.holdoway@firetrust.com>
References: <20070516110810.ba3554fe.steve.holdoway@firetrust.com>

I managed to get this working properly by dropping the index on the url column, and making it the primary key instead. Can anyone point me at docs that define the difference between a primary key and an unique index on the same field? I'm a bit confused here!

Cheers,  Steve

On Wed, 16 May 2007 11:08:10 +1200
Steve Holdoway <steve.holdoway@firetrust.com> wrote:

> Can anyone out there help me, I'm having a bit of a problem trying to improve the performance of a php script that does a data load?
> 
> At the moment, the script checks a database to see if the entry is present, and, if not, it adds it. The table has about 400,000 rows, and I'm checking a bulk load of about 50,000 entries. The table is defined as follows (not my design!):
> 
> Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
> 
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
> 
> db=> \d badurls
>                      Table "public.badurls"
>    Column    |            Type             |     Modifiers
> -------------+-----------------------------+--------------------
>  url         | character varying           | not null
>  ip          | character(20)               |
>  dateadded   | timestamp without time zone | not null
>  type        | character(1)                |
>  publishdate | timestamp without time zone |
>  status      | integer                     | not null default 1
>  version     | integer                     | default 0
>  edited      | integer                     | default 0
>  category    | character(4)[]              |
> Indexes:
>     "idxbadurls_url" hash (url)
>     "idxbadurls_version" btree (version)
> 
> 
> and I'm accessing the url column. (I've tried a btree index as well... not much difference)
> 
> In an attempt to improve the performance, I'm getting a list of data that's already there in a single hit, and not attempting to insert them ( I'm still checking the rest first, just in case... ).
> 
> So, I'm trying to run a select url from badurls where url in ( .... ) to get this list. However, this is also desperately slow...
> 
> 1000 in the list: 14 sec
> 5000 in the list: 2:40
> 10000 in the list: 5:50
> 
> ( run from a client machine over a 100mbit lan ).
> 
> Anyway, that's enough to prove to me that I need to rejig stuff to get this to work!!!
> 
> Server's a dual xeon machine, with hardware raid, and running debian. It's got 4GB of memory, 1GB is currently not even used for io buffers.
> 
> The postgres instance is configured as standard, except for increasing the shared_buffers to 200MB.
> 
> Explain plan for about 50 entries in the 'in' clause returns
> 
> Bitmap Heap Scan on badurls  (cost=4531.22..15748.67 rows=60462 width=32)
>    Recheck Cond: ((url)::text = ANY (('{.....}'::character varying[])::text[]))
>    ->  Bitmap Index Scan on idxbadurls_url  (cost=0.00..4516.10 rows=60462 width=0)
>          Index Cond: ((url)::text = ANY (('{.....}'::character varying[])::text[]))
> (4 rows)
> 
> 
> Can anyone suggest what I should be reconfiguring???
> 
> Cheers,
> 
> 
> Steve
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate



view thread (4+ messages)  latest in thread

Message-ID: <20070519145145.0c16e6fc.steve.holdoway@firetrust.com>
Permalink:  ../20070519145145.0c16e6fc.steve.holdoway@firetrust.com/
Also on:    postgresql.org/message-id/20070519145145.0c16e6fc.steve.holdoway@firetrust.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: steve.holdoway@firetrust.com
  Subject: Re: improve select performance...
  In-Reply-To: <20070519145145.0c16e6fc.steve.holdoway@firetrust.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox