Re: Expected performance of querying 5k records from 4 million records?

On Mon, Jun 18, 2012 at 9:39 AM, Anish Kejariwal <anishkej(at)gmail(dot)com> wrote:

>
> So, it looks like clustering the index appropriately fixes things! Also,
> I'll recreate the index switching the order to (dataset_id, stat_id,data_id)
>
> Just keep in mind that clustering is a one-time operation. Inserts and
updates will change the order of records in the table, so you'll need to
re-cluster periodically to keep performance high if there are a lot of
inserts and updates into the tables. I didn't re-read the thread, but I
seem recall a partitioned table, so assuming you are partitioning in a
manner which keeps the number of partitions that are actively being
inserted/updated on to a minimum, you only need to cluster the active
partitions, which isn't usually terribly painful. Also, if you are bulk
loading data (and not creating random spaces in the table by deleting and
updating), you can potentially order the data on the way into the table to
avoid the need to cluster repeatedly.

--sam