By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE
| id | title |
|---|---|
| 1 | itsy bitsy spider |
| 2 | itsy bitsy |
| 3 | itsy bitsy |
| 4 | row row row your boat |
| 5 | twinkle twinkle |
| 6 | twinkle twinkle little star |
INSERT 0 6
INSERT 0 10000
ALTER TABLE
CREATE INDEX
VACUUM
PREPARE
| QUERY PLAN |
|---|
| Nested Loop (cost=1.39..190546.32 rows=500600 width=168) (actual time=23.066..210.612 rows=11 loops=1) |
| Output: s1.id, s1.title, s1.title_tsv, s2.id, s2.title, s2.title_tsv |
| -> Seq Scan on public.song s2 (cost=0.00..243.06 rows=10006 width=84) (actual time=0.011..2.054 rows=10006 loops=1) |
| Output: s2.id, s2.title, s2.title_tsv |
| -> Bitmap Heap Scan on public.song s1 (cost=1.39..18.52 rows=50 width=84) (actual time=0.020..0.020 rows=0 loops=10006) |
| Output: s1.id, s1.title, s1.title_tsv |
| Recheck Cond: (s1.title_tsv @@ plainto_tsquery(s2.title)) |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx_gin (cost=0.00..1.38 rows=50 width=0) (actual time=0.008..0.008 rows=0 loops=10006) |
| Index Cond: (s1.title_tsv @@ plainto_tsquery(s2.title)) |
| Planning Time: 0.664 ms |
| JIT: |
| Functions: 6 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 0.582 ms, Inlining 0.000 ms, Optimization 1.229 ms, Emission 19.922 ms, Total 21.733 ms |
| Execution Time: 365.554 ms |
EXPLAIN
| id | title | title_tsv | id | title | title_tsv |
|---|---|---|---|---|---|
| 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' | 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' |
| 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' | 2 | itsy bitsy | 'bitsy' 'itsy' |
| 2 | itsy bitsy | 'bitsy' 'itsy' | 2 | itsy bitsy | 'bitsy' 'itsy' |
| 3 | itsy bitsy | 'bitsy' 'itsy' | 2 | itsy bitsy | 'bitsy' 'itsy' |
| 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' | 3 | itsy bitsy | 'bitsy' 'itsy' |
| 2 | itsy bitsy | 'bitsy' 'itsy' | 3 | itsy bitsy | 'bitsy' 'itsy' |
| 3 | itsy bitsy | 'bitsy' 'itsy' | 3 | itsy bitsy | 'bitsy' 'itsy' |
| 4 | row row row your boat | 'boat' 'row' 'your' | 4 | row row row your boat | 'boat' 'row' 'your' |
| 5 | twinkle twinkle | 'twinkle' | 5 | twinkle twinkle | 'twinkle' |
| 6 | twinkle twinkle little star | 'little' 'star' 'twinkle' | 5 | twinkle twinkle | 'twinkle' |
| 6 | twinkle twinkle little star | 'little' 'star' 'twinkle' | 6 | twinkle twinkle little star | 'little' 'star' 'twinkle' |
SELECT 11
PREPARE
| QUERY PLAN |
|---|
| Nested Loop (cost=1.39..191797.07 rows=500550 width=168) (actual time=6.350..194.910 rows=5 loops=1) |
| Output: s1.id, s1.title, s1.title_tsv, s2.id, s2.title, s2.title_tsv |
| -> Seq Scan on public.song s2 (cost=0.00..243.06 rows=10006 width=84) (actual time=0.005..2.050 rows=10006 loops=1) |
| Output: s2.id, s2.title, s2.title_tsv |
| -> Bitmap Heap Scan on public.song s1 (cost=1.39..18.64 rows=50 width=84) (actual time=0.018..0.018 rows=0 loops=10006) |
| Output: s1.id, s1.title, s1.title_tsv |
| Recheck Cond: (s1.title_tsv @@ plainto_tsquery(s2.title)) |
| Filter: (s1.id <> s2.id) |
| Rows Removed by Filter: 0 |
| Heap Blocks: exact=6 |
| -> Bitmap Index Scan on idx_gin (cost=0.00..1.38 rows=50 width=0) (actual time=0.008..0.008 rows=0 loops=10006) |
| Index Cond: (s1.title_tsv @@ plainto_tsquery(s2.title)) |
| Planning Time: 0.217 ms |
| JIT: |
| Functions: 8 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 0.467 ms, Inlining 0.000 ms, Optimization 0.281 ms, Emission 6.005 ms, Total 6.754 ms |
| Execution Time: 195.475 ms |
EXPLAIN
| id | title | title_tsv | id | title | title_tsv |
|---|---|---|---|---|---|
| 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' | 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' |
| 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' | 2 | itsy bitsy | 'bitsy' 'itsy' |
| 2 | itsy bitsy | 'bitsy' 'itsy' | 2 | itsy bitsy | 'bitsy' 'itsy' |
| 3 | itsy bitsy | 'bitsy' 'itsy' | 2 | itsy bitsy | 'bitsy' 'itsy' |
| 1 | itsy bitsy spider | 'bitsy' 'itsy' 'spider' | 3 | itsy bitsy | 'bitsy' 'itsy' |
| 2 | itsy bitsy | 'bitsy' 'itsy' | 3 | itsy bitsy | 'bitsy' 'itsy' |
| 3 | itsy bitsy | 'bitsy' 'itsy' | 3 | itsy bitsy | 'bitsy' 'itsy' |
| 4 | row row row your boat | 'boat' 'row' 'your' | 4 | row row row your boat | 'boat' 'row' 'your' |
| 5 | twinkle twinkle | 'twinkle' | 5 | twinkle twinkle | 'twinkle' |
| 6 | twinkle twinkle little star | 'little' 'star' 'twinkle' | 5 | twinkle twinkle | 'twinkle' |
| 6 | twinkle twinkle little star | 'little' 'star' 'twinkle' | 6 | twinkle twinkle little star | 'little' 'star' 'twinkle' |
SELECT 11