Обсуждение: CLUSTER equivalent

Are the two following options equivalent?

OPTION A (ordered insert):

CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
CREATE INDEX idx1 ON table1(cluster_col);
INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM
table1 ORDER BY cluster_col;

OPTION B (unordered insert followed by CLUSTER):

CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
CREATE INDEX idx1 ON table1(cluster_col);
INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM table1;
CLUSTER idx1 ON table1;

Thanks,
Kevin Murphy

P.S.  On another topic, did I gather correctly from a recent thread that
it would be more efficient to define the above table (if it were really
only two columns) as:

create table clustered_tagged_genes (integer pmid, text mention);

i.e., with the integer field before the text field?

Kevin Murphy <murphy@genome.chop.edu> writes:
> Are the two following options equivalent?
> OPTION A (ordered insert):

> CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
> CREATE INDEX idx1 ON table1(cluster_col);
> INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM
> table1 ORDER BY cluster_col;

> OPTION B (unordered insert followed by CLUSTER):

> CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER);
> CREATE INDEX idx1 ON table1(cluster_col);
> INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM table1;
> CLUSTER idx1 ON table1;

Pretty much, but the first is probably faster.  CLUSTER is not the
speediest possible way of sorting data :-(

> P.S.  On another topic, did I gather correctly from a recent thread that
> it would be more efficient to define the above table (if it were really
> only two columns) as:

> create table clustered_tagged_genes (integer pmid, text mention);

> i.e., with the integer field before the text field?

Yeah, putting fixed-width fields first is usually a (marginal) win.

            regards, tom lane