From: | hubert depesz lubaczewski <depesz(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | How to use brin indexes? |
Date: | 2014-11-21 19:59:13 |
Message-ID: | CAKrjmhdiAC3v=nF3v9FQ3so6MEX+duV=d_c84HVpE7-=ggo+7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)[local]:5930) 20:56:22 [depesz]
$ insert into t select generate_series(1,1000000);
INSERT 0 1000000
(depesz(at)[local]:5930) 20:56:31 [depesz]
$ create index b on t using brin (id);
CREATE INDEX
(depesz(at)[local]:5930) 20:56:42 [depesz]
$ vacuum ANALYZE t;
VACUUM
(depesz(at)[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(at)[local]:5930) 20:56:58 [depesz]
$ drop index b;
DROP INDEX
(depesz(at)[local]:5930) 20:57:22 [depesz]
$ create index b on t using brin (id) with (pages_per_range=1);
CREATE INDEX
(depesz(at)[local]:5930) 20:57:35 [depesz]
$ vacuum ANALYZE t;
VACUUM
(depesz(at)[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?
depesz
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2014-11-21 20:12:55 | Re: How to use brin indexes? |
Previous Message | Andrew Dunstan | 2014-11-21 19:49:26 | Re: psql \sf doesn't show it's SQL when ECHO_HIDDEN is on |