From: AlexK987 <alex.cue.987@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Why is GIN index slowing down my query?
Date: Sun, 1 Feb 2015 15:32:21 -0700 (MST)
Message-ID: <1422829941197-5836319.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I've created a GIN index on an INT[] column, but it slows down the selects.
Here is my table:
create table talent(person_id INT NOT NULL,
skills INT[] NOT NULL);
insert into talent(person_id, skills)
select generate_series, array[0, 1] || generate_series
from generate_series(3, 1048575);
create index talent_skills on talent using gin(skills);
analyze talent;
Here is my select:
explain analyze
select * from talent
where skills <@ array[1, 15]
"Bitmap Heap Scan on talent (cost=52.00..56.01 rows=1 width=37) (actual
time=590.022..590.022 rows=0 loops=1)"
" Recheck Cond: (skills <@ '{1,15}'::integer[])"
" Rows Removed by Index Recheck: 1048573"
" Heap Blocks: exact=8739"
" -> Bitmap Index Scan on talent_skills (cost=0.00..52.00 rows=1 width=0)
(actual time=207.661..207.661 rows=1048573 loops=1)"
" Index Cond: (skills <@ '{1,15}'::integer[])"
"Planning time: 1.310 ms"
"Execution time: 590.078 ms"
If I drop my GIN index, my select is faster:
drop index talent_skills
explain analyze
select * from talent
where skills <@ array[1, 15]
"Seq Scan on talent (cost=0.00..21846.16 rows=1 width=37) (actual
time=347.442..347.442 rows=0 loops=1)"
" Filter: (skills <@ '{1,15}'::integer[])"
" Rows Removed by Filter: 1048573"
"Planning time: 0.130 ms"
"Execution time: 347.470 ms"
Am I missing something?
--
View this message in context: http://postgresql.nabble.com/Why-is-GIN-index-slowing-down-my-query-tp5836319.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: alex.cue.987@gmail.com
Subject: Re: Why is GIN index slowing down my query?
In-Reply-To: <1422829941197-5836319.post@n5.nabble.com>
* 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