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?
Re: Poor man's partitioned index .... not being used? |
Список | 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 по дате отправления: