pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRead performance on Large Table
12+ messages / 6 participants
[nested] [flat]
* Read performance on Large Table
@ 2015-05-21 14:54 Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:02 ` Re: Read performance on Large Table Ravi Krishna <sravikrishna3@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:09 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 16:00 ` Re: Read performance on Large Table Gilberto Castillo <gilberto.castillo@etecsa.cu>
0 siblings, 4 replies; 12+ messages in thread
From: Kido Kouassi @ 2015-05-21 14:54 UTC (permalink / raw)
To: pgsql-admin
Hello Admin,
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....
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
@ 2015-05-21 15:02 ` Ravi Krishna <sravikrishna3@gmail.com>
3 siblings, 0 replies; 12+ messages in thread
From: Ravi Krishna @ 2015-05-21 15:02 UTC (permalink / raw)
To: Kido Kouassi <jjkido@gmail.com>; +Cc: pgsql-admin
Looks like currently the table has no index. It has to do table scan.
Please post the typical queries and we will suggest what columns to index.
On Thu, May 21, 2015 at 10:54 AM, Kido Kouassi <jjkido@gmail.com> wrote:
> Hello Admin,
>
> 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....
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
@ 2015-05-21 15:05 ` Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
3 siblings, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2015-05-21 15:05 UTC (permalink / raw)
To: Kido Kouassi <jjkido@gmail.com>; +Cc: pgsql-admin
On Thu, May 21, 2015 at 8:54 AM, Kido Kouassi <jjkido@gmail.com> wrote:
> Hello Admin,
>
> 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.
These are both good ideas. You can use partitioning to do Step1 and
still have all your data in "one place" so to speak. Here's the
standard page on partitioning:
http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html
I've done a lot of partitioning of big data sets in postgresql and if
there's some common field, like data, that makes sense to partition
on, it can be a huge win.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
@ 2015-05-21 15:18 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2015-05-21 15:18 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Kido Kouassi <jjkido@gmail.com>; pgsql-admin
On May 21, 2015, at 9:05 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> I've done a lot of partitioning of big data sets in postgresql and if
> there's some common field, like data, that makes sense to partition
> on, it can be a huge win.
Indeed. I recently did it on exactly this kind of thing, a log of activity. And the common queries weren’t slow at all.
But if I wanted to upgrade via dump/restore with minimal downtime, rather than set up Slony or try my luck with pg_upgrade, I could dump the historical partitions, drop those tables, then dump/restore, then restore the historical partitions at my convenience. (In this particular db, history is unusually huge compared to the live data.)
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-05-21 15:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:41 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2015-05-21 15:21 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Kido Kouassi <jjkido@gmail.com>; pgsql-admin
On Thu, May 21, 2015 at 9:18 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On May 21, 2015, at 9:05 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> I've done a lot of partitioning of big data sets in postgresql and if
>> there's some common field, like data, that makes sense to partition
>> on, it can be a huge win.
>
> Indeed. I recently did it on exactly this kind of thing, a log of activity. And the common queries weren’t slow at all.
>
> But if I wanted to upgrade via dump/restore with minimal downtime, rather than set up Slony or try my luck with pg_upgrade, I could dump the historical partitions, drop those tables, then dump/restore, then restore the historical partitions at my convenience. (In this particular db, history is unusually huge compared to the live data.)
I use an interesting method to setup partitioning. I setup my
triggers, then insert the data in chunks from the master table to
itself.
insert into master_table select * from only master_table limit 10000;
and run that over and over. The data is all in the same "table" to the
application. But it's slowly moving to the partitions without
interruption.
Note: ALWAYS use triggers for partitioning. Rules are way too slow.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
@ 2015-05-21 15:41 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:45 ` Re: Read performance on Large Table Keith <keith@keithf4.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2015-05-21 15:41 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
On May 21, 2015, at 9:21 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> I use an interesting method to setup partitioning. I setup my
> triggers, then insert the data in chunks from the master table to
> itself.
>
> insert into master_table select * from only master_table limit 10000;
>
> and run that over and over. The data is all in the same "table" to the
> application. But it's slowly moving to the partitions without
> interruption.
Ah, good idea—I’ll have to remember that.
In my case the table is write-mostly, read-rarely, and update-never, so insert into partition select * from master where… with millions of rows at a time was not a problem.
The one thing I did differently from the documentation, is write a pgSQL procedure to create a partition for a particular date range and the function and trigger, where the trigger function checks the date against the current “head” partition, and if the date is newer, calls the function which creates the partition and replaces the trigger function. I hate having to remember those things which only have to be done once a year ;-)
And yes, that particular implementation depends on the fact that the criteria on which I’m partitioning increases monotonically. I expect it would create a performance nightmare if I tried to insert old data into the master table while the application was actively inserting new data. The same basic technique can be adapted to other situations, but the trigger function is slightly more complicated, and the function to create the trigger function would be kind of ugly and hard to follow.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:41 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-05-21 15:45 ` Keith <keith@keithf4.com>
2015-05-21 16:01 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 12+ messages in thread
From: Keith @ 2015-05-21 15:45 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
If you're just going to do basic time series partitioning, I've written a
tool that manages most of it for you. Also does retention management as
well and can dump out the old partitions automatically.
https://github.com/keithf4/pg_partman
I'm in the process of getting v2.0.0 out that has a lot of new work done,
but will only be compatible with Postgres 9.4 (since it uses background
workers to have scheduling built in). So if you want to wait, I should have
that out soon.
On Thu, May 21, 2015 at 11:41 AM, Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> On May 21, 2015, at 9:21 AM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
> >
> > I use an interesting method to setup partitioning. I setup my
> > triggers, then insert the data in chunks from the master table to
> > itself.
> >
> > insert into master_table select * from only master_table limit 10000;
> >
> > and run that over and over. The data is all in the same "table" to the
> > application. But it's slowly moving to the partitions without
> > interruption.
>
> Ah, good idea—I’ll have to remember that.
>
> In my case the table is write-mostly, read-rarely, and update-never, so
> insert into partition select * from master where… with millions of rows at
> a time was not a problem.
>
> The one thing I did differently from the documentation, is write a pgSQL
> procedure to create a partition for a particular date range and the
> function and trigger, where the trigger function checks the date against
> the current “head” partition, and if the date is newer, calls the function
> which creates the partition and replaces the trigger function. I hate
> having to remember those things which only have to be done once a year ;-)
>
> And yes, that particular implementation depends on the fact that the
> criteria on which I’m partitioning increases monotonically. I expect it
> would create a performance nightmare if I tried to insert old data into the
> master table while the application was actively inserting new data. The
> same basic technique can be adapted to other situations, but the trigger
> function is slightly more complicated, and the function to create the
> trigger function would be kind of ugly and hard to follow.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:41 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:45 ` Re: Read performance on Large Table Keith <keith@keithf4.com>
@ 2015-05-21 16:01 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 16:25 ` Re: Read performance on Large Table Keith <keith@keithf4.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2015-05-21 16:01 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
On May 21, 2015, at 9:45 AM, Keith <keith@keithf4.com> wrote:
>
> If you're just going to do basic time series partitioning, I've written a tool that manages most of it for you. Also does retention management as well and can dump out the old partitions automatically.
Yep, make some partitions ahead of time, plus cron to periodically run in order to stay ahead, is an alternative approach.
> I'm in the process of getting v2.0.0 out that has a lot of new work done, but will only be compatible with Postgres 9.4 (since it uses background workers to have scheduling built in). So if you want to wait, I should have that out soon.
I wouldn’t mind that—I did it the way I did partly because I wanted it all self-contained in PG without external (cron or launchd or…) configuration dependencies.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:41 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:45 ` Re: Read performance on Large Table Keith <keith@keithf4.com>
2015-05-21 16:01 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-05-21 16:25 ` Keith <keith@keithf4.com>
2015-05-21 16:34 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 12+ messages in thread
From: Keith @ 2015-05-21 16:25 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
On Thu, May 21, 2015 at 12:01 PM, Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> On May 21, 2015, at 9:45 AM, Keith <keith@keithf4.com> wrote:
> >
> > If you're just going to do basic time series partitioning, I've written
> a tool that manages most of it for you. Also does retention management as
> well and can dump out the old partitions automatically.
>
> Yep, make some partitions ahead of time, plus cron to periodically run in
> order to stay ahead, is an alternative approach.
>
This is exactly what the extension does.
>
> > I'm in the process of getting v2.0.0 out that has a lot of new work
> done, but will only be compatible with Postgres 9.4 (since it uses
> background workers to have scheduling built in). So if you want to wait, I
> should have that out soon.
>
> I wouldn’t mind that—I did it the way I did partly because I wanted it all
> self-contained in PG without external (cron or launchd or…) configuration
> dependencies.
>
The problem with creating partitions on the fly via triggers, even ahead of
when they're needed, is you run into BIG contention issues when you have
high insert rates. One session will see it needs to create a new table, the
next one comes in before the table is made but either gets put into a wait
state or outright fails when it goes to try and make the new table that's
already there. The serial partitioning method in pg_partman does do this by
default (creates new future partitions when current is at 50%) because for
serial, scheduling maintenance can be tricky because you may not be able to
predict how often it needs to run. But several people brought up issues
with the contention problems this caused so I made it optional to use the
scheduled maintenance run instead.
It may not be an issue for you, but it's definitely something to keep in
mind if you notice performance issues. :)
>
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:05 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Re: Read performance on Large Table Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:41 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:45 ` Re: Read performance on Large Table Keith <keith@keithf4.com>
2015-05-21 16:01 ` Re: Read performance on Large Table Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 16:25 ` Re: Read performance on Large Table Keith <keith@keithf4.com>
@ 2015-05-21 16:34 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 12+ messages in thread
From: Scott Ribe @ 2015-05-21 16:34 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
On May 21, 2015, at 10:25 AM, Keith <keith@keithf4.com> wrote:
>
> The problem with creating partitions on the fly via triggers, even ahead of when they're needed, is you run into BIG contention issues when you have high insert rates. One session will see it needs to create a new table, the next one comes in before the table is made but either gets put into a wait state or outright fails when it goes to try and make the new table that's already there…
No failure, I handle the exception.
> It may not be an issue for you, but it's definitely something to keep in mind if you notice performance issues. :)
>
Right. Around midnight this db has very low activity.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
@ 2015-05-21 15:09 ` Scott Ribe <scott_ribe@elevated-dev.com>
3 siblings, 0 replies; 12+ messages in thread
From: Scott Ribe @ 2015-05-21 15:09 UTC (permalink / raw)
To: Kido Kouassi <jjkido@gmail.com>; +Cc: pgsql-admin
On May 21, 2015, at 8:54 AM, Kido Kouassi <jjkido@gmail.com> wrote:
>
> 2: Create an index on the Timestamp column sin it is the one we use the most in queries.
Absolutely. Go ahead right now.
Note that you can use the CONCURRENTLY option to create index, and it will not require a lock on the table, so you can do it while the db is being used. It takes longer, sometimes a lot longer, than the regular create, but who cares? Start it and leave it.
After you’ve done that and seen how much benefit you get just from indexing your most-commonly used column, post a range of common queries back here for further advice.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Read performance on Large Table
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
@ 2015-05-21 16:00 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
3 siblings, 0 replies; 12+ messages in thread
From: Gilberto Castillo @ 2015-05-21 16:00 UTC (permalink / raw)
To: Kido Kouassi <jjkido@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2015-05-21 16:34 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-21 14:54 Read performance on Large Table Kido Kouassi <jjkido@gmail.com>
2015-05-21 15:02 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-21 15:05 ` Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:18 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2015-05-21 15:41 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:45 ` Keith <keith@keithf4.com>
2015-05-21 16:01 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 16:25 ` Keith <keith@keithf4.com>
2015-05-21 16:34 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 15:09 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-21 16:00 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
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