Thread: How to use brin indexes?

On 11/21/2014 09:59 PM, hubert depesz lubaczewski wrote:
> I tried to add BRIN index on very simple table, to test it for "Waiting
> for" blogpost, btu I can't get it to work.
>
> I got newest git head, and ran:
>
> $ create table t (id int8);
> CREATE TABLE
> (depesz@[local]:5930) 20:56:22 [depesz]
> $ insert into t select generate_series(1,1000000);
> INSERT 0 1000000
> (depesz@[local]:5930) 20:56:31 [depesz]
> $ create index b on t using brin (id);
> CREATE INDEX
> (depesz@[local]:5930) 20:56:42 [depesz]
> $ vacuum ANALYZE t;
> VACUUM
> (depesz@[local]:5930) 20:56:49 [depesz]
> $ explain analyze select * from t where id = 1224;
>                                             QUERY
> PLAN
> ------------------------------------------------------------------------------------------------
>   Seq Scan on t  (cost=0.00..16925.00 rows=1 width=8) (actual
> time=0.278..59.242 rows=1 loops=1)
>     Filter: (id = 1224)
>     Rows Removed by Filter: 999999
>   Planning time: 0.186 ms
>   Execution time: 59.272 ms
> (5 rows)
>
> (depesz@[local]:5930) 20:56:58 [depesz]
> $ drop index b;
> DROP INDEX
> (depesz@[local]:5930) 20:57:22 [depesz]
> $ create index b on t using brin (id) with (pages_per_range=1);
> CREATE INDEX
> (depesz@[local]:5930) 20:57:35 [depesz]
> $ vacuum ANALYZE t;
> VACUUM
> (depesz@[local]:5930) 20:57:38 [depesz]
> $ explain analyze select * from t where id = 1224;
>                                             QUERY
> PLAN
> ------------------------------------------------------------------------------------------------
>   Seq Scan on t  (cost=0.00..16925.00 rows=1 width=8) (actual
> time=0.124..56.876 rows=1 loops=1)
>     Filter: (id = 1224)
>     Rows Removed by Filter: 999999
>   Planning time: 0.044 ms
>   Execution time: 56.886 ms
> (5 rows)
>
> What is the problem? Why isn't the brin index used?

The minmax operator families don't include any cross-type operators. I'm 
not exactly sure why not.. Alvaro?

Anyway, try "select * from t where id = 1224::int8"

- Heikki

hubert depesz lubaczewski wrote:
> > The minmax operator families don't include any cross-type operators. I'm
> not exactly sure why not.. Alvaro?
> > Anyway, try "select * from t where id = 1224::int8"
> 
> It works that way, but it would be great to have it working with non-casted
> values too.

Yeah.  I didn't put in much extra effort because some simple experiments
worked with cross-type queries; my guess at the time was that the system
added casts automatically for some cases and that was good enough.  But
there are several examples that don't work all that nicely, so I'm going
to rework the opfamilies a bit.  Stay tuned.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera wrote:
> hubert depesz lubaczewski wrote:
> > > The minmax operator families don't include any cross-type operators. I'm
> > not exactly sure why not.. Alvaro?
> > > Anyway, try "select * from t where id = 1224::int8"
> >
> > It works that way, but it would be great to have it working with non-casted
> > values too.
>
> Yeah.  I didn't put in much extra effort because some simple experiments
> worked with cross-type queries; my guess at the time was that the system
> added casts automatically for some cases and that was good enough.  But
> there are several examples that don't work all that nicely, so I'm going
> to rework the opfamilies a bit.  Stay tuned.

This patch reworks the opfamilies in pretty much the same way btree has
them: each type category has one opfamily, and there are opclasses for
each datatype; then there are enough pg_amop and pg_amproc entries to
cover all the cross-type operators and necessary support functions.
The collapsed types, and the resulting opfamilies, are:

int2, int4, int8 -> integer_minmax_ops
date, timestamp, timestamptz -> datetime_minmax_ops
float4, float8 -> float_minmax_ops

I'm not sure if there should be something involving numeric.  Btree
doesn't appear to have it.

Btree also has a "network" opfamily which I initially guessed to contain
inet and cidr, but on looking closer it only has inet.  And Brin doesn't
have cidr at all, so I left this alone.

I won't push this right away because I want to add the cross-type stuff
to the tests, to ensure I haven't bollixed anything; I ran a few quick
manual tests and everything seems to work.  But if Depesz wants to test
the behavior, be my guest.  Note that you need to initdb after
rebuilding with this patch.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Sat, Nov 22, 2014 at 3:29 AM, Alvaro Herrera <span
dir="ltr"><<ahref="mailto:alvherre@2ndquadrant.com" target="_blank">alvherre@2ndquadrant.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I
won'tpush this right away because I want to add the cross-type stuff<br /> to the tests, to ensure I haven't bollixed
anything;I ran a few quick<br /> manual tests and everything seems to work.  But if Depesz wants to test<br /> the
behavior,be my guest.  Note that you need to initdb after<br /> rebuilding with this patch.<br /></blockquote></div><br
/></div><divclass="gmail_extra">Tested. Works OK.<br /><br /></div><div class="gmail_extra">depesz<br /><br
/></div></div>

hubert depesz lubaczewski wrote:
> On Sat, Nov 22, 2014 at 3:29 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
> 
> > I won't push this right away because I want to add the cross-type stuff
> > to the tests, to ensure I haven't bollixed anything; I ran a few quick
> > manual tests and everything seems to work.  But if Depesz wants to test
> > the behavior, be my guest.  Note that you need to initdb after
> > rebuilding with this patch.
> 
> Tested. Works OK.

Thanks for testing.  I have pushed it now.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services