Use of index for 50% column restriction
От | Bruce Momjian |
---|---|
Тема | Use of index for 50% column restriction |
Дата | |
Msg-id | 20160608172109.GA2152@momjian.us обсуждение исходный текст |
Ответы |
Re: Use of index for 50% column restriction
|
Список | pgsql-hackers |
As part of my research on the parsing/planning behavior of PREPARE, I found a surprising behavior --- a WHERE clause that is 50% restrictive is using an index. I thought only <10% restrictions used indexes. To setup the test: DROP TABLE IF EXISTS test;CREATE TABLE test (c1 INT, c2 INT, c3 INT);INSERT INTO test SELECT c1, 0, 0 FROM generate_series(1,10000) AS a(c1);INSERT INTO test SELECT c1, 1, 1 FROM generate_series(10001, 20000) AS a(c1);CREATE INDEXi_test_c2 ON test (c2);ANALYZE test;EXPLAIN SELECT * FROM test WHERE c2 = 0; The output is: QUERY PLAN ----------------------------------------------------------------------------- Index Scan using i_test_c2 on test (cost=0.29..349.29rows=10000 width=12) ---------- Index Cond: (c2 = 0) (2 rows) \timing does show the optimizer is making the right decision to use the index, and this behavior is the same back to at least 9.3. Setting effective_cache_size = '8kB' does not change this behavior. What am I missing? Is my 10% assumption wrong? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
В списке pgsql-hackers по дате отправления: