Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 5A43F9FB588 for ; Tue, 15 May 2007 20:20:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 25848-08 for ; Tue, 15 May 2007 20:20:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from weatherbeeta.com.au (218-214-212-153.people.net.au [218.214.212.153]) by postgresql.org (Postfix) with SMTP id D00989FBD03 for ; Tue, 15 May 2007 20:20:48 -0300 (ADT) Received: (qmail 12684 invoked from network); 15 May 2007 23:20:41 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;64da8c085d774b973df3ad39925b5354; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 15 May 2007 23:20:41 -0000 Received: (qmail 25071 invoked by uid 1166); 16 May 2007 09:20:34 +1000 Received: from 172.20.0.155 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.0.155):. Processed in 0.266962 secs); 15 May 2007 23:20:34 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):. Processed in 0.266962 secs Process 25065) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 16 May 2007 09:20:32 +1000 From: "Phillip Smith" To: "'Steve Holdoway'" , Subject: Re: improve select performance... Date: Wed, 16 May 2007 09:19:16 +1000 Organization: Weatherbeeta Pty Ptd Message-ID: <003f01c79747$74e49c80$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <20070516110810.ba3554fe.steve.holdoway@firetrust.com> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1179271241.241-12677-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.03/0.08 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/124 X-Sequence-Number: 25444 Can you post the actual queries and the full EXPLAIN ANALYZE for the queries? Are you running 64bit Debian and PostgreSQL? Cheers, ~p -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Steve Holdoway Sent: Wednesday, 16 May 2007 09:08 To: pgsql-admin@postgresql.org Subject: [ADMIN] improve select performance... 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=3D> \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=3D4531.22..15748.67 rows=3D60462 = width=3D32) Recheck Cond: ((url)::text =3D ANY (('{.....}'::character varying[])::text[])) -> Bitmap Index Scan on idxbadurls_url (cost=3D0.00..4516.10 = rows=3D60462 width=3D0) Index Cond: ((url)::text =3D 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 *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments