Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YvRz8-0004yA-KW for pgsql-admin@arkaria.postgresql.org; Thu, 21 May 2015 15:02:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YvRz7-0002XJ-Uq for pgsql-admin@arkaria.postgresql.org; Thu, 21 May 2015 15:02:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YvRz7-0002XD-4r for pgsql-admin@postgresql.org; Thu, 21 May 2015 15:02:09 +0000 Received: from imx3.etecsa.cu ([200.55.152.137]) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1YvRyr-0006t8-D5 for pgsql-admin@postgresql.org; Thu, 21 May 2015 15:02:08 +0000 Received: from Unknown ([192.168.91.42] helo=mn2.etecsa.cu) by imx3.etecsa.cu with ESMTP id tB0US45f59129; Thu, 21 May 2015 11:00:30 -0400 Received: (qmail 11936 invoked by uid 11185); 21 May 2015 16:00:59 -0000 Received: from 192.168.91.6 by mn2.etecsa.cu (envelope-from , uid 502) with qmail-scanner-2.08 (spamassassin: 3.2.5. Clear:RC:1(192.168.91.6):. Processed in 0.054057 secs); 21 May 2015 16:00:59 -0000 Received: from unknown (HELO webmail.etecsa.cu) ([192.168.91.6]) (envelope-sender ) by mn2.etecsa.cu (qmail-ldap-1.03) with SMTP for ; 21 May 2015 16:00:59 -0000 Received: from 192.168.207.54 (SquirrelMail authenticated user gilberto.castillo) by webmail.etecsa.cu with HTTP; Thu, 21 May 2015 11:00:58 -0500 (GMT+5) Message-ID: <43242.192.168.207.54.1432224058.squirrel@webmail.etecsa.cu> In-Reply-To: References: Date: Thu, 21 May 2015 11:00:58 -0500 (GMT+5) Subject: Re: Read performance on Large Table From: "Gilberto Castillo" To: "Kido Kouassi" Cc: "pgsql-admin@postgresql.org" Reply-To: gilberto.castillo@etecsa.cu User-Agent: SquirrelMail/1.4.8-5.el5_4.10 MIME-Version: 1.0 X-Priority: 3 (Normal) Importance: Normal X-SpamTest-Envelope-From: gilberto.castillo@etecsa.cu X-SpamTest-Info: Profiles 72422 [Jan 28 2015] X-SpamTest-Method: none X-SpamTest-Rate: 0 X-SpamTest-Status: Not detected X-SpamTest-Status-Extended: not_detected X-SpamTest-Version: SMTP-Filter Version 3.0.0 [0285], KAS30/SDK/Release X-Anti-Virus: Kaspersky Mail Gateway, version: 5.6.28/RELEASE, bases: 20140401T050822 #7726142, check: 20150521 clean Content-Type: multipart/mixed; boundary="=tB0USuDW61773=imx3.etecsa.cu=" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --=tB0USuDW61773=imx3.etecsa.cu= Content-Disposition: inline Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit > Hello Admin, Hi Kido, Create indece in the fields that you use to compare the JOIN > I am looking to build a database to log activities on my system > > Currently I have the following table: > > CREATE TABLE table > ( > attr1 bigint, > attr2 text, > attr3 timestamp without time zone, > attr4 integer, > attr5 character varying(50), > attr6 character varying(50), > attr7 character varying(50), > attr8 character varying(50), > attr9 character varying(50), > attr10 character varying(50) > ) > > Over 3 months the table has grown to 600+ Millions Rows, > > We mainly do inserts, we almost never do updates or delete. > > However the table is not very useful as it stands because any select we > run it takes a very long time ( in hours) to complete. > > > Question1: Is Slowness to be expected with such big table? > Question2: is there anything I am not doing that I should do to get a > better perfomance? > > I am thinking about 2 solution but I wanted to Ask the more experienced > people before implementing them: > > 1: Break the table into multiple small table for each week. > 2: Create an index on the Timestamp column sin it is the one we use the > most in queries. > > Thank you in advance for you Help, > > --Kido K. > > > Note: > explain analyse select count(*) from table where attr5='value' and attr3 > > > '05/19/2015 00:00:00' and attr3 < '05/21/2015 00:00:00'; > > Has been running for 30 minutes and counting.... Saludos, Gilberto Castillo ETECSA, La Habana, Cuba --=tB0USuDW61773=imx3.etecsa.cu= Content-Type: text/plain Content-Disposition: inline --- This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu Visit our web-site: , --=tB0USuDW61773=imx3.etecsa.cu= Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin --=tB0USuDW61773=imx3.etecsa.cu=--