To: "pgsql-performance." <pgsql-performance@postgresql.org>
From: Gunther <raj@gusw.net>
Subject: Poor man's partitioned index .... not being used?
Message-ID: <a8c2185f-0da6-6130-e9b8-0bfee3320528@gusw.net>
Date: Wed, 20 Mar 2019 22:45:07 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.5.3
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------D0B0337050F0DDF83BEBB707"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------D0B0337050F0DDF83BEBB707
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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


--------------D0B0337050F0DDF83BEBB707
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <pre>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 &lt; 194064;
CREATE INDEX
foo=# CREATE UNIQUE INDEX Test_pk1 ON Test(id) WHERE id &gt;= 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

</pre>
  </body>
</html>

--------------D0B0337050F0DDF83BEBB707--