Description
PostgreSQL 9.6 on Linux, size of tags_tmp
table ~ 30 GB (10 million rows), tags
is a text[]
and has only 6 values.
tags_tmp(id int, tags text[], maker_date timestamp, value text)
id tags maker_date value
1 {a,b,c} 2016-11-09 This is test
2 {a} 2016-11-08 This is test
3 {b,c} 2016-11-07 This is test
4 {c} 2016-11-06 This is test
5 {d} 2016-11-05 This is test
I need to retrieve data with filter on tags
as well as order by
on maker_date desc
. Can I create an index on both tags & maker_date desc
columns?
If not, could you suggest other ideas?
Query example
select id, tags, maker_date, value
from tags_tmp
where tags && array['a','b']
order by maker_date desc
limit 5 offset 0
SQL code:
create index idx1 on tags_tmp using gin (tags);
create index idx2 on tags_tmp using btree(maker_date desc);
explain (analyse on, costs on, verbose)
select id, tags, maker_date, value
from tags_tmp
where tags && array['funny','inspiration']
order by maker_date desc
limit 5 offset 0 ;
Explain result:
Limit (cost=233469.63..233469.65 rows=5 width=116) (actual time=801.482..801.483 rows=5 loops=1)
Output: id, tags, maker_date, value
-> Sort (cost=233469.63..234714.22 rows=497833 width=116) (actual time=801.481..801.481 rows=5 loops=1)
Output: id, tags, maker_date, value
Sort Key: tags_tmp.maker_date DESC
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on public.tags_tmp (cost=6486.58..225200.81 rows=497833 width=116) (actual time=212.982..696.650 rows=366392 loops=1)
Output: id, tags, maker_date, value
Recheck Cond: (tags_tmp.tags && '{funny,inspiration}'::text[])
Heap Blocks: exact=120034
-> Bitmap Index Scan on idx1 (cost=0.00..6362.12 rows=497882 width=0) (actual time=171.742..171.742 rows=722612 loops=1)
Index Cond: (tags_tmp.tags && '{funny,inspiration}'::text[])
Planning time: 0.185 ms
Execution time: 802.128 ms
More information
I tested with using partial index for only one tag, of course, it's faster. But I have many tag , for example: create index idx_tmp on tags_tmp using btree (maker_date desc) where (tags && array['tag1') or tags && array['tag2'] or ... or tags && array['tag6']
. And I tested between tags && array['tag1']
and 'tag1' = any(tags)
, performance is same.
text[]
has only 6 values =a, b, c, d, e, f
. For example:tags={a,b,c}, tags={a}, tags={a,c}, tags={a,b,c,d,e,f}, tags={b,f}
and so on. But it cannot has valueg->z, A-Z
and etc.create table tags_tmp(id int primary key not null, tags text[] not null, maker_date timestamp not null, value text)
In terms of
distinct
array values , thetags
which containsa
takes 20% rows of tablewhere 'a' = any(tags)
, b=20%where 'b' = any(tags)
, c=20%where 'c' = any(tags)
, d=20%where 'd' = any(tags)
, e=10%where 'e' = any(tags)
,f=10%where 'f' = any(tags)
.In addition,
(tags, maker_date)
is not unique.This table is not read only.
It is
sort on timestamp
, but my example shows dates, sorry about that.
Current situation: tags = 'a' or tags = 'b' or tags = 'c'
and more
(1) With GIN index
or convert text[] to int[]
as well as convert text[] to int
and more, it will use bitmap index on multi tags.
Finally, after testing, I decided to use old solution, change OR
into many UNION
clauses, each UNION
will limit the number of data. Of course, I will create partial index
for each tag value as well as I can combine with (1) above .
In terms of OFFSET
, it will use one or more condition in WHERE
clause instead.
Example
EXPLAIN (ANALYSE ON, costs ON, VERBOSE)
SELECT rs.*
FROM (
(SELECT tags,
id,
maker_date
FROM tags_tmp
WHERE 'a' = any(tags)
AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
ORDER BY maker_date DESC LIMIT 5)
UNION
(SELECT tags,
id,
maker_date
FROM tags_tmp
WHERE 'b' = any(tags)
AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
ORDER BY maker_date DESC LIMIT 5)
UNION
(SELECT tags,
id,
maker_date
FROM tags_tmp
WHERE 'c' = any(tags)
AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
ORDER BY maker_date DESC LIMIT 5)) rs
ORDER BY rs.maker_date DESC LIMIT 5 ;
a:2016-11-09
,b:2016-11-09
,c:2016-11-09
as tree nodes and all of them include a pointer to row#1
. MongoDB actually supports compound multikey indexes... Unfortunately PostgreSQL does not, and this is very annoying. You would have to create a separate table withid_ref | tag | date
in order to create a similar b-tree. – collimarco Jun 28 '19 at 17:10