Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F24219FB33A for ; Sat, 13 Jan 2007 22:40:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 57383-09 for ; Sat, 13 Jan 2007 22:40:16 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from globalrelay.com (mail1.globalrelay.com [204.92.49.200]) by postgresql.org (Postfix) with ESMTP id 4F5BF9FB337 for ; Sat, 13 Jan 2007 22:40:21 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [71.215.208.64] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 122511831; Sat, 13 Jan 2007 18:40:50 -0800 From: "Dave Dutcher" To: "'Mark Dobbrow'" , Subject: Re: Large table performance Date: Sat, 13 Jan 2007 20:40:18 -0600 Message-ID: <010701c73785$54a02d30$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0108_01C73753.0A05BD30" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6822 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Thread-Index: Acc2qkmSy4jGl7RDRDyYjf8zPQxM7AA2Q0eg In-Reply-To: <376735.48483.qm@web35309.mail.mud.yahoo.com> Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.168 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200701/216 X-Sequence-Number: 22817 This is a multi-part message in MIME format. ------=_NextPart_000_0108_01C73753.0A05BD30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Have you run vacuum and analyze on the table? What version of Postgres = are you running? What OS are you using? =20 This looks like a straight forward query. With any database the first = time you run the query its going to be slower because it actually has to read = off disk. The second time its faster because some or all of the = data/indexes will be cached. However 10 seconds sounds like a long time for pulling 10,000 records out of a table of 3 million. If you post an EXPLAIN = ANALYZE, it might give us a clue. =20 Dave =20 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mark = Dobbrow Sent: Friday, January 12, 2007 6:31 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Large table performance Hello -=20 I have a fairly large table (3 million records), and am fetching 10,000 non-contigous records doing a simple select on an indexed column ie=20 select grades from large_table where teacher_id =3D X This is a test database, so the number of records is always 10,000 and i have 300 different teacher ids. The problem is, sometimes fetching un-cached records takes 0.5 secs and sometimes (more often) is takes more like 10.0 seconds (fetching the same records for a given teacher_id a second time takes = about 0.25 secs) Has anyone seen similar behavior or know what the solution might be? any help much appreciated, Mark ps. My shared_buffers is set at 5000 (kernal max), and work_mem=3D8192 ------=_NextPart_000_0108_01C73753.0A05BD30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
Have=20 you run vacuum and analyze on the table?  What version of Postgres = are you=20 running?  What OS are you using?
 
This=20 looks like a straight forward query.  With any database the first = time you=20 run the query its going to be slower because it actually has to read off = disk.  The second time its faster because some or all of the = data/indexes=20 will be cached.  However 10 seconds sounds like a long time for = pulling=20 10,000 records out of a table of 3 million.  If you post an EXPLAIN = ANALYZE, it might give us a clue.
 
Dave
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Mark=20 Dobbrow
Sent: Friday, January 12, 2007 6:31 PM
To: = pgsql-performance@postgresql.org
Subject: [PERFORM] Large = table=20 performance

Hello -

I have a fairly large = table (3=20 million records), and am fetching 10,000 non-contigous records doing a = simple=20 select on an indexed column ie

select grades from large_table = where=20 teacher_id =3D X

This is a test database, so the number of = records is=20 always 10,000 and i have 300 different teacher ids.

The problem = is,=20 sometimes fetching un-cached records takes 0.5 secs and sometimes = (more often)=20 is takes more like 10.0 seconds

(fetching the same records for = a given=20 teacher_id a second time takes about 0.25 secs)

Has anyone seen = similar=20 behavior or know what the solution might be?

any help much=20 appreciated,
Mark



ps. My shared_buffers is set at = 5000=20 (kernal max), and = work_mem=3D8192


------=_NextPart_000_0108_01C73753.0A05BD30--