Poor man's partitioned index .... not being used?

Поиск
Список
Период
Сортировка
От Gunther
Тема Poor man's partitioned index .... not being used?
Дата
Msg-id a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net
обсуждение исходный текст
Ответы Re: Poor man's partitioned index .... not being used?  (David Rowley <david.rowley@2ndquadrant.com>)
Re: Poor man's partitioned index .... not being used?  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Список pgsql-performance
Hi all, look at this short story please:

foo=# CREATE TABLE Test(id int NOT NULL PRIMARY KEY);
CREATE TABLE
foo=# INSERT INTO test SELECT row_number() OVER() FROM pg_class a CROSS JOIN pg_class b;
INSERT 0 388129
foo=# EXPLAIN SELECT * FROM Test WHERE id = '8934';                               QUERY PLAN
---------------------------------------------------------------------------Index Only Scan using test_pkey on test  (cost=0.42..8.44 rows=1 width=4)  Index Cond: (id = 8934)
(2 rows)

foo=# ALTER TABLE Test DROP CONSTRAINT Test_pkey;
ALTER TABLE
foo=# EXPLAIN SELECT * FROM Test WHERE id = '8934';                      QUERY PLAN
-------------------------------------------------------Seq Scan on test  (cost=0.00..6569.61 rows=1 width=4)  Filter: (id = 8934)
(2 rows)

foo=# SELECT max(id)/2 FROM Test;?column?
----------  194064
(1 row)

foo=# CREATE UNIQUE INDEX Test_pk0 ON Test(id) WHERE id < 194064;
CREATE INDEX
foo=# CREATE UNIQUE INDEX Test_pk1 ON Test(id) WHERE id >= 194064;
CREATE INDEX
foo=# ANALYZE Test;
ANALYZE
foo=# EXPLAIN SELECT * FROM Test WHERE id = 8934;                               QUERY PLAN
--------------------------------------------------------------------------Index Only Scan using test_pk0 on test  (cost=0.42..8.44 rows=1 width=4)  Index Cond: (id = 8934)
(2 rows)


foo=# DROP INDEX Test_pk0;
DROP INDEX
foo=# DROP INDEX Test_pk1;
DROP INDEX

foo=# CREATE UNIQUE INDEX Test_pk0 ON Test(id) WHERE mod(id,2) = 0;
CREATE INDEX
foo=# CREATE UNIQUE INDEX Test_pk1 ON Test(id) WHERE mod(id,2) = 1;
CREATE INDEX
foo=# ANALYZE Test;
ANALYZE
foo=# EXPLAIN SELECT * FROM Test WHERE id = '8934';                     QUERY PLAN
-------------------------------------------------------Seq Scan on test  (cost=0.00..6569.61 rows=1 width=4)  Filter: (id = 8934)
(2 rows)

Why is that index never used?

PS: there is a performance question behind this, big table, heavily used index, 
the hope was that with this simple scheme of partitioning just the index one might
distribute the load better. I know, if the load really is so big, why not partition
the entire table. But just for hecks, why not this way?

regards,
-Gunther

В списке pgsql-performance по дате отправления:

Предыдущее
От: Stephan Schmidt
Дата:
Сообщение: AW: Performance issue with order by clause on
Следующее
От: David Rowley
Дата:
Сообщение: Re: Poor man's partitioned index .... not being used?