Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9DBA12E0184 for ; Thu, 13 Dec 2007 14:04:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 59224-04 for ; Thu, 13 Dec 2007 14:04:43 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id 3DCEE2E0083 for ; Thu, 13 Dec 2007 14:04:50 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so1285457waf.5 for ; Thu, 13 Dec 2007 10:04:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=mwc0D17axzZFGJ4B0QCpR57IGnGAwCw7EKS6Yk3C7Ek=; b=iPU36zn2+BFbpoNj9cTbQK8C01dykIpftfojHBFg/gqQEND6PnI+w/CGTlPfOD4KQFOxUlSUADE6KwEJA9352uqfV5alaoS9ltFDMInZndMlx8dF8qZ8Ro2QXknmyboZOHJNUDnhHXbx+CYdJRzfIhb51+594UkWmQY+Bpt4GyU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=IeG8Xoeukn2GLcdCPq3s+t93tNx+ZDpWagwm7+/7RO7QmevK/ock3dB0Dx4WXeYgbgX+utaKnC51uzgqUnPqTuKGZ0hj2kxgkBmWDh/lL5X9bbl9VG8rj9vuf+Mi++e+VjIp0zdFfux72avOcqrCNX/FkMrGs86ZOQ/y+TWAzfw= Received: by 10.114.38.2 with SMTP id l2mr2569817wal.89.1197569087886; Thu, 13 Dec 2007 10:04:47 -0800 (PST) Received: by 10.114.131.4 with HTTP; Thu, 13 Dec 2007 10:04:47 -0800 (PST) Message-ID: Date: Thu, 13 Dec 2007 12:04:47 -0600 From: "Scott Marlowe" To: "olivier boissard" Subject: Re: improve performance in a big table Cc: A.Burbello , pgsql-admin@postgresql.org In-Reply-To: <47613061.9000004@cerene.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <222808.5599.qm@web36215.mail.mud.yahoo.com> <47613061.9000004@cerene.fr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/88 X-Sequence-Number: 27605 On Dec 13, 2007 7:15 AM, olivier boissard wrot= e: > A.Burbello a =E9crit : > > Hi people, > > > > I have a case and I got a doubt what should be the > > best ways. > > > > One table has more than 150 million of rows, and I > > thought that could divided by state. > > For each row has person ID, state and other > > informations, but the search would be done only by > > person ID (number column). > > > > I can improve the query by putting index in that > > column, but is there any other ways? > > I also studies how to improve performances on big tables. > Like you , I don't know how to improve without index. It's the only way > I found . > I find postgresql is fast on small table but I got real performance > problem when increases the number of rows > Do anyone know if there is specific postgresql tuning parameters in > .conf file for big tables ? > > max_fsm_pages ? > max_fsm_relations ? More often than not, the answer lies not in tuning but in rearranging how you think of your data and how you create indexes. If you guys post some schema and queries (with explain analyze) that aren't running so fast, we'll try to help. although pgsql-perform is the better place to do that.