I have a table with around 100M rows. It only gets data inserted once/day but we need to do
selects a lot. The
selects are usually simple but need to return 100s of thousands of rows sometimes.
It's unique based on three columns
hour which are integer, timestamp, integer respectively. It was slow for most queries but I clustered it to
pricedate and that fixed the slowness for most queries. Those queries were of the type:
select * from mytable where node_id in (1,2,3,4)
We still occasionally need to do queries like:
select * from mytable where pricedate>='2016-05-01'
These are still slow because it's clustered by
node_id first. We have an index on
pricedate already. The issue is that the users often need enough data that the query engine throws out the index and uses a seq scan. Once it's using a seq scan, it benefits greatly from having the data clustered in the way that it is being queried. This leads to the problem that I have where some queries benefit from one clustering and other queries from the other:
It would be nice if there was a way to have two physical copies of the table where one copy is clustered one way and the other is clustered another but user access to it appears as though there is only 1 table and the DB engine would ensure they're in sync. Obviously there'd be write penalties in doing this but that's inconsequential for our usage.
Would something like this be possible?
I'm guessing there isn't a built in way to do what I describe. To do it anyway I guess I'd make a table called
mytable_dup with the same unique key constraint but with the alternate clustering and then setup triggers to insert to it whenever the master is inserted/updated/deleted. That seems doable but from here, would there be a reasonable way to
select from the duplicated table that will be efficient?
I'm running PostgreSQL 9.4 at home and 9.5 on Google.