From: Gilberto Castillo <gilberto.castillo@etecsa.cu>
To: Kido Kouassi <jjkido@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Read performance on Large Table
Date: Thu, 21 May 2015 11:00:58 -0500 (GMT+5)
Message-ID: <43242.192.168.207.54.1432224058.squirrel@webmail.etecsa.cu> (raw)
In-Reply-To: <CA+ebzukaZJVOjw2Vqg0cuCUqbaEEHHuCa-pNbsX6CWAZyga3Fw@mail.gmail.com>
References: <CA+ebzukaZJVOjw2Vqg0cuCUqbaEEHHuCa-pNbsX6CWAZyga3Fw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
> 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
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gilberto.castillo@etecsa.cu, jjkido@gmail.com
Subject: Re: Read performance on Large Table
In-Reply-To: <43242.192.168.207.54.1432224058.squirrel@webmail.etecsa.cu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox