From: Gunther <raj@gusw.net>
To: pgsql-performance. <pgsql-performance@postgresql.org>
Subject: Poor man's partitioned index .... not being used?
Date: Wed, 20 Mar 2019 22:45:07 -0400
Message-ID: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: raj@gusw.net
Subject: Re: Poor man's partitioned index .... not being used?
In-Reply-To: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox