Table with active and historical data

On Wed, Jun 1, 2011 at 7:30 PM, Robert James <srobertjames@gmail.com> wrote:

I have a table with a little active data and a lot of historical data.
 I'd like to be able to access the active data very quickly - quicker
than an index.  Here are the details:

1. Table has about 1 million records
2. Has a column active_date - on a given date, only about 1% are
active.  active_date is indexed and clustered on.
3. Many of my queries are WHERE active_date = today.  Postgres uses
the index for these, but still lakes quite a lot of time.  I repeat
these queries regularly.

can we see a query and its 'explain analyze' that you think takes a lot of time?

4. I'd like to somehow presort or partition the data so that Postgres
doesn't have to do an index scan each time.  I'm not sure how to do
this? Idea?  I know it can be done with inheritance and triggers (
http://stackoverflow.com/questions/994882/what-is-a-good-way-to-horizontal-shard-in-postgresql
), but that method looks a little too complex for me.  I'm looking for
something simple.

an index scan should be good enough, but if it isn't you can look at
partitioning. let's make sure that's really necessary before doing it
however.

5. Finally, I should point out that I still do a large number of
queries on historical data as well.

What do you recommend? Ideas? Also: Why doesn't cluster on active_date
solve the problem? Specifically, if I run SELECT * FROM full_table
WHERE active_date = today, I get a cost of 3500.  If I first select
those records into a new table, and then do SELECT * on the new table,
I get a cost of 64.  Why is that? Why doesn't clustering pregroup
them?

clustering is a tool that allows you to control which tuples are
grouped together on pages -- if you are pulling up more than one tuple
a time hopefully you can reduce the total number of pages you have to
scan by doing it. The bigger the table is, the more that matters.

merlin

Hello,

I think, one good thing to do is partionning, you have already mentioned that in
your mail,
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

try to run also vaccuum command it might help in increasing the performance.

create a boolean flag i.e. active and create an index on it. I think this will
be faster than having index on date field

Regards

________________________________
From: Robert James <srobertjames@gmail.com>
To: Postgres General <pgsql-general@postgresql.org>
Cc: srobertjames@gmail.com
Sent: Thu, June 2, 2011 1:30:11 AM
Subject: [GENERAL] Table with active and historical data

I have a table with a little active data and a lot of historical data.
I'd like to be able to access the active data very quickly - quicker
than an index. Here are the details:

1. Table has about 1 million records
2. Has a column active_date - on a given date, only about 1% are
active. active_date is indexed and clustered on.
3. Many of my queries are WHERE active_date = today. Postgres uses
the index for these, but still lakes quite a lot of time. I repeat
these queries regularly.
4. I'd like to somehow presort or partition the data so that Postgres
doesn't have to do an index scan each time. I'm not sure how to do
this? Idea? I know it can be done with inheritance and triggers (
http://stackoverflow.com/questions/994882/what-is-a-good-way-to-horizontal-shard-in-postgresql

), but that method looks a little too complex for me. I'm looking for
something simple.
5. Finally, I should point out that I still do a large number of
queries on historical data as well.

What do you recommend? Ideas? Also: Why doesn't cluster on active_date
solve the problem? Specifically, if I run SELECT * FROM full_table
WHERE active_date = today, I get a cost of 3500. If I first select
those records into a new table, and then do SELECT * on the new table,
I get a cost of 64. Why is that? Why doesn't clustering pregroup
them?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general