I'm reading Markus Winand's slides: I tested this by the steps below:

1. create a table with column_1 integer and column_2 integer
2. run the commands below:

INSERT INTO dev.table_name(column_1, column_2)
SELECT RANDOM() * 10, RANDOM() * 10 FROM generate_series(1, 10000);

create index idx on dev.table_name(column_1, column_2);

explain (buffers, analyze, verbose) select * from dev.table_name where column_2=2;

explain (buffers, analyze, verbose) select * from dev.table_name where column_1=2;

explain (buffers, analyze, verbose) select * from dev.table_name where column_1=2 and column_2= 2;

However, query for column_1 and column_2 use both index only scan instead of column_1 use index and column_2 use sequential scan as expected from the slides

My question is how am I able to test the importance of column order? Does this not work because I use integer columns? I tried deleting the cache using sync && sudo purge as well as discard all, but it is still the same.

CodePudding user response：

Indexes are good for finding needles in a haystack. They are not so spectacular at finding needles in a pin cushion.

You should make the conditions more selective, as well as make the table larger.

INSERT INTO dev.table_name(column_1, column_2)
SELECT RANDOM() * 1000, RANDOM() * 1000 FROM generate_series(1, 1000000);