X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F168F528A8 for ; Mon, 6 Jun 2005 13:20:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26673-03 for ; Mon, 6 Jun 2005 16:20:40 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 7C97D52891 for ; Mon, 6 Jun 2005 13:20:38 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 7D861413AE8; Mon, 6 Jun 2005 17:20:25 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 56A7315ED5; Mon, 6 Jun 2005 17:18:09 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25464-02; Mon, 6 Jun 2005 17:18:04 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 6F4D615ED7; Mon, 6 Jun 2005 17:09:55 +0100 (BST) Message-ID: <42A47553.5060408@archonet.com> Date: Mon, 06 Jun 2005 17:09:55 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "ON.KG" Cc: pgsql-sql Subject: Re: What is faster? References: <42A43791.7030306@archonet.com> <17434586625.20050606202531@on.kg> <42A4616E.8070101@archonet.com> <15836788703.20050606210213@on.kg> In-Reply-To: <15836788703.20050606210213@on.kg> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.345 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HOT_NASTY X-Spam-Level: X-Archive-Number: 200506/85 X-Sequence-Number: 21686 ON.KG wrote: > Hi All! > >>>What is faster - SLECTion data from one large table (200 000 - 300 000 >>>records), or SELECTion from a few small tables (example, 2 tables 150 >>>000 records each)? > > RH> It depends. Are you selecting all records? One record? A few records? If > RH> one or a few, do you have a suitable index on the table(s)? Is the table > RH> clustered? Expected to be cached in RAM? > > RH> Do you have a specific problem, or reason to believe you may encounter one? > > It's a real problem > > For example i have two large tables > Structure of tables is same - has two fields - id, ip > > Now i'm using two selection from each in one transaction > Each of them selects only one record > selection clase like WHERE ip = 'xxx.xxx.xxx.xxx' > so it is searches existance of IP in each table > > tables are clustered OK - so the tables aren't updated frequently, I assume. Do you have an index on "ip"? > about cached in RAM - i'm novice in Postgresql - how does it work? The operating-system will keep frequently used disk-blocks in memory. You don't have to do anything. Have you done any performance tuning in your postgresql.conf file? If not, try reading: http://www.powerpostgresql.com/PerfList > now i need to make much faster as it is possible > and have an idea just merge two tables in one - will it help me? If they hold the same information, they probably shouldn't have been split in the first place. -- Richard Huxton Archonet Ltd