Subject: Re: Indexes with condition using immutable functions applied to
	column not used
From: Sylvain Rabot <sylvain@abstraction.fr>
Reply-To: sylvain@abstraction.fr
To: pgsql-performance@postgresql.org
In-Reply-To: <1297124098.2549.65.camel@kheops>
References: <1297124098.2549.65.camel@kheops>
Content-Type: multipart/signed; micalg="pgp-sha1";
	protocol="application/pgp-signature";
	boundary="=-aSoqW5WKGFgzIhpREDZj"
Date: Tue, 08 Feb 2011 21:08:54 +0100
Message-ID: <1297195734.2497.93.camel@kheops>
Mime-Version: 1.0


--=-aSoqW5WKGFgzIhpREDZj
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I also tried to do table partitioning using the same immutable function,
it works well except for constraint exclusion.

CREATE TABLE mike.directory_part_0 () INHERITS (mike.directory) WITH (fillf=
actor =3D 90);
CREATE RULE directory_part_0_insert AS ON INSERT TO mike.directory WHERE (_=
_mod_cons_hash(new.id_user::bigint, 2) =3D 0)
DO INSTEAD INSERT INTO mike.directory_part_0 VALUES (new.*);

CREATE TABLE mike.directory_part_1 () INHERITS (mike.directory) WITH (fillf=
actor =3D 90);
CREATE RULE directory_part_1_insert AS ON INSERT TO mike.directory WHERE (_=
_mod_cons_hash(new.id_user::bigint, 2) =3D 1)
DO INSTEAD INSERT INTO mike.directory_part_1 VALUES (new.*);

mike_part=3D# explain analyze select * from directory where id_user =3D 3;
                                                                           =
          QUERY PLAN                                                       =
                              =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------
 Result  (cost=3D0.00..310.21 rows=3D5226 width=3D141) (actual time=3D0.080=
..7.583 rows=3D2653 loops=3D1)
   ->  Append  (cost=3D0.00..310.21 rows=3D5226 width=3D141) (actual time=
=3D0.077..3.654 rows=3D2653 loops=3D1)
         ->  Index Scan using directory_id_user_btree_idx on directory  (co=
st=3D0.00..8.27 rows=3D1 width=3D141) (actual time=3D0.007..0.007 rows=3D0 =
loops=3D1)
               Index Cond: (id_user =3D 3)
         ->  Index Scan using directory_part_0_id_user_btree_idx on directo=
ry_part_0 directory  (cost=3D0.00..8.27 rows=3D1 width=3D150) (actual time=
=3D0.035..0.035 rows=3D0 loops=3D1)
               Index Cond: (id_user =3D 3)
         ->  Index Scan using directory_part_1_id_user_btree_idx on directo=
ry_part_1 directory  (cost=3D0.00..293.67 rows=3D5224 width=3D141) (actual =
time=3D0.035..2.037 rows=3D2653 loops=3D1)
               Index Cond: (id_user =3D 3)
 Total runtime: 8.807 ms
(9 rows)


On Tue, 2011-02-08 at 01:14 +0100, Sylvain Rabot wrote:
> Hi,
>=20
> I am trying to understand how indexes works to get the most of them.
>=20
> First I would like to know if there is more advantage than overhead to
> split an index in several ones using conditions e.g. doing :
>=20
> CREATE INDEX directory_id_user_0_btree_idx ON mike.directory USING btree =
(id_user) WHERE id_user < 250000;
> CREATE INDEX directory_id_user_250000_btree_idx ON mike.directory USING b=
tree (id_user) WHERE id_user >=3D 250000 AND id_user < 500000;
> CREATE INDEX directory_id_user_500000_btree_idx ON mike.directory USING b=
tree (id_user) WHERE id_user >=3D 500000 AND id_user < 750000;
> CREATE INDEX directory_id_user_750000_btree_idx ON mike.directory USING b=
tree (id_user) WHERE id_user >=3D 750000 AND id_user < 1000000;
>=20
> instead of having only one index for all the id_user. the forecasts for
> the table directory are +500 millions records and something like 1
> million distinct id_user.
>=20
> If there is my idea was to do a repartition in the indexes using a
> consistent hash algorithm in order to fill the indexes in parallel
> instead of successively :
>=20
> CREATE OR REPLACE FUNCTION mike.__mod_cons_hash(
>     IN  in_dividend     bigint,
>     IN  in_divisor      integer,
>     OUT remainder       integer
> ) AS $__$
>=20
> BEGIN
>     SELECT in_dividend % in_divisor INTO remainder;
> END;
>=20
> $__$ LANGUAGE plpgsql IMMUTABLE COST 10;
>=20
> CREATE INDEX directory_id_user_mod_cons_hash_0_btree_idx ON mike.director=
y USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 0;
> CREATE INDEX directory_id_user_mod_cons_hash_1_btree_idx ON mike.director=
y USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 1;
> CREATE INDEX directory_id_user_mod_cons_hash_2_btree_idx ON mike.director=
y USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 2;
> CREATE INDEX directory_id_user_mod_cons_hash_3_btree_idx ON mike.director=
y USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 3;
>=20
> But the thing is the indexes are not used :
>=20
> mike=3D# SELECT version();
>                                                       version            =
                                         =20
> -------------------------------------------------------------------------=
------------------------------------------
>  PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubu=
ntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit
> (1 row)
>=20
> mike=3D# REINDEX INDEX directory_id_user_mod_cons_hash_0_btree_idx;
> LOG:  duration: 14644.160 ms  statement: REINDEX INDEX
> directory_id_user_mod_cons_hash_0_btree_idx;
> REINDEX
> mike=3D# EXPLAIN ANALYZE SELECT * FROM directory WHERE id_user =3D 4;
>                                                    QUERY PLAN            =
                                      =20
> -------------------------------------------------------------------------=
---------------------------------------
>  Seq Scan on directory  (cost=3D0.00..38140.66 rows=3D67 width=3D148) (ac=
tual time=3D0.077..348.211 rows=3D10303 loops=3D1)
>    Filter: (id_user =3D 4)
>  Total runtime: 351.114 ms
> (3 rows)
>=20
> So I also did this test :
>=20
> mike=3D# CREATE INDEX directory_id_user_4_btree_idx ON mike.directory USI=
NG btree (id_user) WHERE id_user > 3 and id_user < 5;
> CREATE INDEX
> mike=3D# EXPLAIN ANALYZE select * from directory where id_user =3D 4;
>                                                                    QUERY =
PLAN                                                                   =20
> -------------------------------------------------------------------------=
------------------------------------------------------------------------
>  Index Scan using directory_id_user_4_btree_idx on directory  (cost=3D0.0=
0..10.58 rows=3D67 width=3D148) (actual time=3D0.169..7.753 rows=3D10303 lo=
ops=3D1)
>    Index Cond: (id_user =3D 4)
>  Total runtime: 10.973 ms
> (3 rows)
>=20
> mike=3D# DROP INDEX directory_id_user_4_btree_idx;
> DROP INDEX
> mike=3D# CREATE INDEX directory_id_user_4_btree_idx ON mike.directory USI=
NG btree (id_user) WHERE id_user - 1 > 2 and id_user + 1 < 6;
> CREATE INDEX
> mike=3D# EXPLAIN ANALYZE select * from directory where id_user =3D 4;
>                                                    QUERY PLAN            =
                                      =20
> -------------------------------------------------------------------------=
---------------------------------------
>  Seq Scan on directory  (cost=3D0.00..38140.66 rows=3D67 width=3D148) (ac=
tual time=3D0.153..360.020 rows=3D10303 loops=3D1)
>    Filter: (id_user =3D 4)
>  Total runtime: 363.106 ms
> (3 rows)
>=20
> mike=3D# DROP INDEX directory_id_user_4_btree_idx;
> DROP INDEX
> mike=3D# CREATE INDEX directory_id_user_4_btree_idx ON mike.directory USI=
NG btree (id_user) WHERE id_user > 2 + 1 and id_user < 6 - 1;
> CREATE INDEX
> mike=3D# EXPLAIN ANALYZE select * from directory where id_user =3D 4;
>                                                                    QUERY =
PLAN                                                                   =20
> -------------------------------------------------------------------------=
------------------------------------------------------------------------
>  Index Scan using directory_id_user_4_btree_idx on directory  (cost=3D0.0=
0..10.58 rows=3D67 width=3D148) (actual time=3D0.245..8.262 rows=3D10303 lo=
ops=3D1)
>    Index Cond: (id_user =3D 4)
>  Total runtime: 11.110 ms
> (3 rows)
>=20
> As you see the index condition although, differently written, is the
> same but the second index is not used apparently because the immutable
> function is applied on the column.
>=20
> So do you know the reason why the planner is not able to use indexes
> which have immutable functions applied to the column in their
> condition ?
>=20
> Regards.
>=20

--=20
Sylvain Rabot <sylvain@abstraction.fr>

--=-aSoqW5WKGFgzIhpREDZj
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iQEcBAABAgAGBQJNUaLWAAoJECLlHVUnhaoWX4EIAKFaPGrjNNRb8DjaXrGFwVk7
IeP4f7DUJGB8OZ4BYvocvP9McD8C+EYQPfT1q6xuawbk+6CpFkBypMrvJOKOhKt5
9t0uTb7K2G99+JIKVevHMGydgucM4+G28Zt621jHm23OZGPCI45/4vLLJ4Nz9Nzy
lj4X/BMlroKW1C5D7a6eMUUfUlb/js6gWW/jSEhADQSFE9Uq/OLPcudvtkJNUjWd
FWYNsozTE/gh1Kw2AHyRsYYzl59NzkV6znpan7jvnwdJYRa4zIKqv1Y1oyZBndBB
2/MQIzp8Iberf6Kw18wrZYYCrcaBw9c8mdIkkyJ47YY3bMO0GGzrSAK2jxs3Gss=
=DEH0
-----END PGP SIGNATURE-----

--=-aSoqW5WKGFgzIhpREDZj--