DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=KAbheVyUNbzsIQf8Kqp+XzCoGH84VuKSsz6vGy/6B/Gp03YgN5nHqRbtw0/OPqYfAJ
	4imkgJvMPM3IX/N0JIABsNSe7Q2OW2lh0zDe2g0vkp01KM+MZd2peD3pQlZJt2+HdgAY
	d9LxiQ4tbhGIlT8pGJ0M4NnZz37Rxk9BJyonI=
MIME-Version: 1.0
Date: Wed, 1 Jun 2011 19:54:35 -0400
Message-ID: <BANLkTim93LKid3efencyeC-ASObCJuwzNA@mail.gmail.com>
Subject: CLUSTER versus a dedicated table
From: Robert James <srobertjames@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

Hi.  I'm interested in understanding the differences between
CLUSTERing a table and making a dedicated one.

We have a table with about 1 million records.  On a given day, only
about 1% of them are of interest.  That 1% changes every day (it's
WHERE active_date = today), and so we index and cluster on it.

Even so, the planner shows a very large cost for the Index Scan: about
3500.  If I instead do a SELECT INTO temp_table FROM big_table WHERE
active_date = today, and then do SELECT * FROM temp_table, I get a
planned cost of 65.  Yet, the actual time for both queries is almost
identical.

Questions:
1. Why is there such a discrepancy between the planner's estimate and
the actual cost?

2. In a case like this, will I in general see a performance gain by
doing a daily SELECT INTO and then querying from that table? My ad hoc
test doesn't indicate I would (despite the planner's prediction), and
I'd rather avoid this if it won't help.

3. In general, does CLUSTER provide all the performance benefits of a
dedicated table? If it doesn't, what does it lack?

Thank you.