Skip to content

Instantly share code, notes, and snippets.

@anarchivist

anarchivist/00_before_drop_index.txt

Created April 8, 2016 15:45
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save anarchivist/5e80fc27702d650fd43b9bfb9e5a3aff to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save anarchivist/5e80fc27702d650fd43b9bfb9e5a3aff to your computer and use it in GitHub Desktop.
Download ZIP
Raw
00_before_drop_index.txt
```
marmotta=# explain analyze verbose SELECT U1.U1 AS V1
marmotta-# FROM ((SELECT P1.subject AS U1
marmotta(# FROM triples P1
marmotta(# WHERE P1.deleted = false
marmotta(# AND P1.predicate = 568948697535369218
marmotta(# AND P1.object = 717423266866630665
marmotta(# AND NOT (EXISTS (SELECT P1.subject AS V1, _P1.object AS V1
marmotta(# FROM triples _P1
marmotta(# WHERE _P1.deleted = false
marmotta(# AND P1.subject = _P1.subject
marmotta(# AND _P1.predicate = 692406936894017536
marmotta(#
marmotta(# ))
marmotta(#
marmotta(# ) UNION (SELECT P1.subject AS U1
marmotta(# FROM triples P1
marmotta(# WHERE P1.deleted = false
marmotta(# AND P1.predicate = 584134991467008000
marmotta(# AND P1.object = 717423266866630665
marmotta(# AND NOT (EXISTS (SELECT P1.subject AS V1, _P1.object AS V1
marmotta(# FROM triples _P1
marmotta(# WHERE _P1.deleted = false
marmotta(# AND P1.subject = _P1.subject
marmotta(# AND _P1.predicate = 692406936894017536
marmotta(#
marmotta(# ))
marmotta(#
marmotta(# )) AS U1
marmotta-# ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------
HashAggregate (cost=187.63..187.66 rows=3 width=8) (actual time=924.029..947.178 rows=80072 loops=1)
Output: p1.subject
Group Key: p1.subject
-> Append (cost=1.14..187.62 rows=3 width=8) (actual time=0.176..869.338 rows=80072 loops=1)
-> Nested Loop Anti Join (cost=1.14..95.59 rows=2 width=8) (actual time=0.176..760.045 rows=80040 loops=1)
Output: p1.subject
-> Index Scan using triples_object_idx on public.triples p1 (cost=0.57..88.15 rows=2 width=8) (actua
l time=0.102..341.963 rows=80040 loops=1)
Output: p1.id, p1.subject, p1.predicate, p1.object, p1.context, p1.creator, p1.inferred, p1.deleted, p1.createdat, p1.deletedat
Index Cond: (p1.object = 717423266866630665::bigint)
Filter: (p1.predicate = 568948697535369218::bigint)
Rows Removed by Filter: 32
-> Index Scan using idx_triples_spo on public.triples _p1 (cost=0.57..3.59 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=80040)
Output: _p1.id, _p1.subject, _p1.predicate, _p1.object, _p1.context, _p1.creator, _p1.inferred, _p1.deleted, _p1.createdat, _p1.deletedat
Index Cond: ((p1.subject = _p1.subject) AND (_p1.predicate = 692406936894017536::bigint))
-> Nested Loop Anti Join (cost=1.14..92.00 rows=1 width=8) (actual time=85.592..85.841 rows=32 loops=1)
Output: p1_1.subject
-> Index Scan using triples_object_idx on public.triples p1_1 (cost=0.57..88.15 rows=1 width=8) (actual time=85.573..85.614 rows=32 loops=1)
Output: p1_1.id, p1_1.subject, p1_1.predicate, p1_1.object, p1_1.context, p1_1.creator, p1_1.inferred, p1_1.deleted, p1_1.createdat, p1_1.deletedat
Index Cond: (p1_1.object = 717423266866630665::bigint)
Filter: (p1_1.predicate = 584134991467008000::bigint)
Rows Removed by Filter: 80040
-> Index Scan using idx_triples_spo on public.triples _p1_1 (cost=0.57..3.59 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=32)
Output: _p1_1.id, _p1_1.subject, _p1_1.predicate, _p1_1.object, _p1_1.context, _p1_1.creator, _p1_1.inferred, _p1_1.deleted, _p1_1.createdat, _p1_1.deletedat
Index Cond: ((p1_1.subject = _p1_1.subject) AND (_p1_1.predicate = 692406936894017536::bigint))
Planning time: 143.404 ms
Execution time: 957.942 ms
(26 rows)
```
Raw
01_after_drop_index.txt
```
marmotta=# explain analyze verbose SELECT U1.U1 AS V1
marmotta-# FROM ((SELECT P1.subject AS U1
marmotta(# FROM triples P1
marmotta(# WHERE P1.deleted = false
marmotta(# AND P1.predicate = 568948697535369218
marmotta(# AND P1.object = 717423266866630665
marmotta(# AND NOT (EXISTS (SELECT P1.subject AS V1, _P1.object AS V1
marmotta(# FROM triples _P1
marmotta(# WHERE _P1.deleted = false
marmotta(# AND P1.subject = _P1.subject
marmotta(# AND _P1.predicate = 692406936894017536
marmotta(#
marmotta(# ))
marmotta(#
marmotta(# ) UNION (SELECT P1.subject AS U1
marmotta(# FROM triples P1
marmotta(# WHERE P1.deleted = false
marmotta(# AND P1.predicate = 584134991467008000
marmotta(# AND P1.object = 717423266866630665
marmotta(# AND NOT (EXISTS (SELECT P1.subject AS V1, _P1.object AS V1
marmotta(# FROM triples _P1
marmotta(# WHERE _P1.deleted = false
marmotta(# AND P1.subject = _P1.subject
marmotta(# AND _P1.predicate = 692406936894017536
marmotta(#
marmotta(# ))
marmotta(#
marmotta(# )) AS U1
marmotta-# ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------
HashAggregate (cost=188.00..188.03 rows=3 width=8) (actual time=672.477..694.646 rows=80654 loops=1)
Output: p1.subject
Group Key: p1.subject
-> Append (cost=1.27..187.99 rows=3 width=8) (actual time=0.050..626.244 rows=80654 loops=1)
-> Nested Loop Anti Join (cost=1.27..95.84 rows=2 width=8) (actual time=0.050..518.229 rows=80622 loops=1)
Output: p1.subject
-> Index Scan using triples_object_idx on public.triples p1 (cost=0.57..88.15 rows=2 width=8) (actual time=0.029..111.735 rows=80622 loops=1)
Output: p1.id, p1.subject, p1.predicate, p1.object, p1.context, p1.creator, p1.inferred, p1.deleted, p1.createdat, p1.deletedat
Index Cond: (p1.object = 717423266866630665::bigint)
Filter: (p1.predicate = 568948697535369218::bigint)
Rows Removed by Filter: 32
-> Index Scan using idx_triples_spoc on public.triples _p1 (cost=0.70..3.71 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=80622)
Output: _p1.id, _p1.subject, _p1.predicate, _p1.object, _p1.context, _p1.creator, _p1.inferred, _p1.deleted, _p1.createdat, _p1.deletedat
Index Cond: ((p1.subject = _p1.subject) AND (_p1.predicate = 692406936894017536::bigint))
-> Nested Loop Anti Join (cost=1.27..92.12 rows=1 width=8) (actual time=83.399..83.636 rows=32 loops=1)
Output: p1_1.subject
-> Index Scan using triples_object_idx on public.triples p1_1 (cost=0.57..88.15 rows=1 width=8) (actual time=83.384..83.423 rows=32 loops=1)
Output: p1_1.id, p1_1.subject, p1_1.predicate, p1_1.object, p1_1.context, p1_1.creator, p1_1.inferred, p1_1.deleted, p1_1.createdat, p1_1.deletedat
Index Cond: (p1_1.object = 717423266866630665::bigint)
Filter: (p1_1.predicate = 584134991467008000::bigint)
Rows Removed by Filter: 80622
-> Index Scan using idx_triples_spoc on public.triples _p1_1 (cost=0.70..3.71 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=32)
Output: _p1_1.id, _p1_1.subject, _p1_1.predicate, _p1_1.object, _p1_1.context, _p1_1.creator, _p1_1.inferred, _p1_1.deleted, _p1_1.createdat, _p1_1.deletedat
Index Cond: ((p1_1.subject = _p1_1.subject) AND (_p1_1.predicate = 692406936894017536::bigint))
Planning time: 146.862 ms
Execution time: 705.180 ms
(26 rows)
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment