On Wed, Jun 1, 2011 at 7:30 PM, Robert James <srobertjames@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
