Skip to content

Instantly share code, notes, and snippets.

@coussej

coussej/bench.sql

Last active November 20, 2021 06:12
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 5 You must be signed in to fork a gist
  • Save coussej/80c385332ce37df6687f to your computer and use it in GitHub Desktop.
Save coussej/80c385332ce37df6687f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
bench.sql
-- clean up database from previous test
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
-- create EAV tables
CREATE TABLE entity (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT
);
CREATE TABLE entity_attribute (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE entity_attribute_value (
id SERIAL PRIMARY KEY,
entity_id INT REFERENCES entity(id),
entity_attribute_id INT REFERENCES entity_attribute(id),
value TEXT
);
-- create entities
INSERT INTO entity(name, description)
SELECT 'entity_' || i, 'Test entity no. ' || i
FROM generate_series(1, 10000000) AS i;
-- create attributes
INSERT INTO entity_attribute(name)
VALUES
('color') -- id = 1
, ('lenght') -- id = 2
, ('width') -- id = 3
, ('hassomething') -- id = 4
, ('country'); -- id = 5
-- insert default values for entities and attributes
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 1, 'red' FROM generate_series(1, 10000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 2, '120' FROM generate_series(1, 10000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 3, '3.1882420' FROM generate_series(1, 10000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 4, 'true' FROM generate_series(1, 10000000) AS i;
INSERT INTO entity_attribute_value(entity_id, entity_attribute_id, value)
SELECT i, 5, 'Belgium' FROM generate_series(1, 10000000) AS i;
-- create JSONB table
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
-- insert the same data
INSERT INTO entity_jsonb (name, description, properties)
SELECT 'entity_' || i, 'Test entity no. ' || i,
json_build_object( 'color','red',
'lenght', 120,
'width', 3.1882420,
'hassomething', true,
'country', 'Belgium'
)
FROM generate_series(1, 10000000) AS i;
-- VACUUM ANALYZE;
VACUUM ANALYZE entity;
VACUUM ANALYZE entity_attribute;
VACUUM ANALYZE entity_attribute_value;
VACUUM ANALYZE entity_jsonb;
SELECT 'Update some records' AS NextTest;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"color"}', '"blue"') WHERE id = 120;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"hassomething"}', 'false') WHERE id = 1200;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"lenght"}', '256') WHERE id = 20;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"width"}', '1.20') WHERE id = 5012;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'blue' WHERE entity_attribute_id = 1 AND entity_id = 120;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'false' WHERE entity_attribute_id = 4 AND entity_id = 1200;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '256' WHERE entity_attribute_id = 2 AND entity_id = 20;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '1.20' WHERE entity_attribute_id = 3 AND entity_id = 5012;
SELECT 'Select all entity_names that have a certain property' AS NextTest;
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
-- REPEAT WITH INDEXES
CREATE INDEX ON entity_attribute_value (entity_id);
CREATE INDEX ON entity_attribute_value (entity_attribute_id);
CREATE INDEX ON entity_attribute (name);
CREATE INDEX ON entity_jsonb USING GIN (properties);
-- VACUUM ANALYZE;
VACUUM ANALYZE entity;
VACUUM ANALYZE entity_attribute;
VACUUM ANALYZE entity_attribute_value;
VACUUM ANALYZE entity_jsonb;
SELECT 'Update some records (with indexes)' AS NextTest;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"color"}', '"blue"') WHERE id = 121;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"hassomething"}', 'false') WHERE id = 1201;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"lenght"}', '256') WHERE id = 21;
EXPLAIN ANALYSE UPDATE entity_jsonb SET properties = jsonb_set(properties, '{"width"}', '1.20') WHERE id = 5013;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'blue' WHERE entity_attribute_id = 1 AND entity_id = 121;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = 'false' WHERE entity_attribute_id = 4 AND entity_id = 1201;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '256' WHERE entity_attribute_id = 2 AND entity_id = 21;
EXPLAIN ANALYSE UPDATE entity_attribute_value SET value = '1.20' WHERE entity_attribute_id = 3 AND entity_id = 5013;
SELECT 'Select all entity_names that have a certain property (with indexes)' AS NextTest;
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'color' = 'blue';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"color": "blue"}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties ->> 'hassomething' = 'false';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT name FROM entity_jsonb WHERE properties @> '{"hassomething": false}';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
EXPLAIN ANALYSE SELECT e.name FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'hassomething' AND eav.value = 'false';
Raw
results.txt
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 10000000
INSERT 0 5
INSERT 0 10000000
INSERT 0 10000000
INSERT 0 10000000
INSERT 0 10000000
INSERT 0 10000000
CREATE TABLE
INSERT 0 10000000
VACUUM
VACUUM
VACUUM
VACUUM
nexttest
---------------------
Update some records
(1 row)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.199..0.199 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.125..0.126 rows=1 loops=1)
Index Cond: (id = 120)
Planning time: 0.354 ms
Execution time: 0.533 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.054..0.054 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.026..0.026 rows=1 loops=1)
Index Cond: (id = 1200)
Planning time: 0.044 ms
Execution time: 0.072 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (id = 20)
Planning time: 0.040 ms
Execution time: 0.054 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.052..0.052 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.025..0.026 rows=1 loops=1)
Index Cond: (id = 5012)
Planning time: 0.039 ms
Execution time: 0.069 ms
(5 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=6926.809..6926.809 rows=0 loops=1)
-> Seq Scan on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=0.036..6926.694 rows=1 loops=1)
Filter: ((entity_attribute_id = 1) AND (entity_id = 120))
Rows Removed by Filter: 49999999
Planning time: 0.375 ms
Execution time: 6926.853 ms
(6 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=5928.414..5928.414 rows=0 loops=1)
-> Seq Scan on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=3429.713..5928.266 rows=1 loops=1)
Filter: ((entity_attribute_id = 4) AND (entity_id = 1200))
Rows Removed by Filter: 49999999
Planning time: 0.126 ms
Execution time: 5928.457 ms
(6 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=5872.168..5872.168 rows=0 loops=1)
-> Seq Scan on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=1167.220..5872.064 rows=1 loops=1)
Filter: ((entity_attribute_id = 2) AND (entity_id = 20))
Rows Removed by Filter: 49999999
Planning time: 0.103 ms
Execution time: 5872.210 ms
(6 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=6077.848..6077.848 rows=0 loops=1)
-> Seq Scan on entity_attribute_value (cost=0.00..1049208.52 rows=1 width=18) (actual time=2320.931..6077.727 rows=1 loops=1)
Filter: ((entity_attribute_id = 3) AND (entity_id = 5012))
Rows Removed by Filter: 49999999
Planning time: 0.098 ms
Execution time: 6077.887 ms
(6 rows)
nexttest
------------------------------------------------------
Select all entity_names that have a certain property
(1 row)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=4096.766..4096.768 rows=1 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999999
Planning time: 1.142 ms
Execution time: 4096.790 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=3709.679..3709.681 rows=1 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999999
Planning time: 0.063 ms
Execution time: 3709.707 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=3884.417..3884.419 rows=1 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999999
Planning time: 0.072 ms
Execution time: 3884.445 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=3963.257..3963.259 rows=1 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999999
Planning time: 0.068 ms
Execution time: 3963.284 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=4461.120..4461.122 rows=1 loops=1)
Filter: (properties @> '{"color": "blue"}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.066 ms
Execution time: 4461.145 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=4064.083..4064.085 rows=1 loops=1)
Filter: (properties @> '{"color": "blue"}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.062 ms
Execution time: 4064.112 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=4240.730..4240.732 rows=1 loops=1)
Filter: (properties @> '{"color": "blue"}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.056 ms
Execution time: 4240.756 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=4183.703..4183.705 rows=1 loops=1)
Filter: (properties @> '{"color": "blue"}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.063 ms
Execution time: 4183.728 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=3793.805..3793.807 rows=1 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999999
Planning time: 0.073 ms
Execution time: 3793.835 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=4237.569..4237.571 rows=1 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999999
Planning time: 0.079 ms
Execution time: 4237.599 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=3835.671..3835.673 rows=1 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999999
Planning time: 0.070 ms
Execution time: 3835.697 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.65 rows=50000 width=14) (actual time=3492.515..3492.517 rows=1 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999999
Planning time: 0.064 ms
Execution time: 3492.540 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=4218.513..4218.515 rows=1 loops=1)
Filter: (properties @> '{"hassomething": false}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.055 ms
Execution time: 4218.539 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=3777.934..3777.936 rows=1 loops=1)
Filter: (properties @> '{"hassomething": false}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.055 ms
Execution time: 3777.959 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=3751.897..3751.899 rows=1 loops=1)
Filter: (properties @> '{"hassomething": false}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.055 ms
Execution time: 3751.922 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..357455.54 rows=10000 width=14) (actual time=3746.944..3746.946 rows=1 loops=1)
Filter: (properties @> '{"hassomething": false}'::jsonb)
Rows Removed by Filter: 9999999
Planning time: 0.059 ms
Execution time: 3746.968 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6449.725..6449.730 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6449.704..6449.707 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6449.642..6449.643 rows=1 loops=1)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
Planning time: 3.409 ms
Execution time: 6449.775 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6479.287..6479.291 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6479.269..6479.271 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6479.233..6479.234 rows=1 loops=1)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
Planning time: 0.425 ms
Execution time: 6479.330 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6721.040..6721.044 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6721.020..6721.022 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6720.754..6720.755 rows=1 loops=1)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.058..0.059 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
Planning time: 0.439 ms
Execution time: 6721.085 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6714.018..6714.023 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6714.000..6714.003 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6713.963..6713.965 rows=1 loops=1)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.023..0.023 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
Planning time: 0.487 ms
Execution time: 6714.063 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6497.699..6497.703 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6497.680..6497.683 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6497.538..6497.540 rows=1 loops=1)
Filter: (value = 'false'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.129..0.129 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
Planning time: 0.439 ms
Execution time: 6497.743 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6703.365..6703.369 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6703.346..6703.348 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6703.313..6703.314 rows=1 loops=1)
Filter: (value = 'false'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
Planning time: 0.483 ms
Execution time: 6703.410 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6576.652..6576.655 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6576.633..6576.635 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6576.599..6576.600 rows=1 loops=1)
Filter: (value = 'false'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
Planning time: 0.440 ms
Execution time: 6576.695 ms
(13 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.43..924215.14 rows=1 width=14) (actual time=6628.036..6628.041 rows=1 loops=1)
Join Filter: (eav.entity_attribute_id = ea.id)
-> Nested Loop (cost=0.43..924214.06 rows=1 width=18) (actual time=6628.011..6628.013 rows=1 loops=1)
-> Seq Scan on entity_attribute_value eav (cost=0.00..924205.60 rows=1 width=8) (actual time=6627.968..6627.969 rows=1 loops=1)
Filter: (value = 'false'::text)
Rows Removed by Filter: 49999999
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.028..0.029 rows=1 loops=1)
Index Cond: (id = eav.entity_id)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
Planning time: 0.438 ms
Execution time: 6628.085 ms
(13 rows)
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
VACUUM
VACUUM
VACUUM
VACUUM
nexttest
------------------------------------
Update some records (with indexes)
(1 row)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.282..0.282 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.066..0.067 rows=1 loops=1)
Index Cond: (id = 121)
Planning time: 0.548 ms
Execution time: 0.334 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.100..0.100 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.034..0.036 rows=1 loops=1)
Index Cond: (id = 1201)
Planning time: 0.095 ms
Execution time: 0.143 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.088..0.088 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.029..0.031 rows=1 loops=1)
Index Cond: (id = 21)
Planning time: 0.068 ms
Execution time: 0.128 ms
(5 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Update on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.145..0.145 rows=0 loops=1)
-> Index Scan using entity_jsonb_pkey on entity_jsonb (cost=0.43..8.46 rows=1 width=159) (actual time=0.083..0.085 rows=1 loops=1)
Index Cond: (id = 5013)
Planning time: 0.044 ms
Execution time: 0.197 ms
(5 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.314..0.314 rows=0 loops=1)
-> Index Scan using entity_attribute_value_entity_id_idx on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.083..0.269 rows=1 loops=1)
Index Cond: (entity_id = 121)
Filter: (entity_attribute_id = 1)
Rows Removed by Filter: 4
Planning time: 0.749 ms
Execution time: 0.340 ms
(7 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.210..0.210 rows=0 loops=1)
-> Index Scan using entity_attribute_value_entity_id_idx on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.152..0.182 rows=1 loops=1)
Index Cond: (entity_id = 1201)
Filter: (entity_attribute_id = 4)
Rows Removed by Filter: 4
Planning time: 0.099 ms
Execution time: 0.254 ms
(7 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.163..0.163 rows=0 loops=1)
-> Index Scan using entity_attribute_value_entity_id_idx on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.084..0.138 rows=1 loops=1)
Index Cond: (entity_id = 21)
Filter: (entity_attribute_id = 2)
Rows Removed by Filter: 4
Planning time: 0.088 ms
Execution time: 0.189 ms
(7 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.188..0.188 rows=0 loops=1)
-> Index Scan using entity_attribute_value_entity_id_idx on entity_attribute_value (cost=0.56..24.03 rows=1 width=18) (actual time=0.116..0.162 rows=1 loops=1)
Index Cond: (entity_id = 5013)
Filter: (entity_attribute_id = 3)
Rows Removed by Filter: 4
Planning time: 0.081 ms
Execution time: 0.232 ms
(7 rows)
nexttest
---------------------------------------------------------------------
Select all entity_names that have a certain property (with indexes)
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.239..4343.836 rows=2 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999998
Planning time: 0.055 ms
Execution time: 4343.890 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.115..3904.883 rows=2 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999998
Planning time: 0.106 ms
Execution time: 3904.922 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.093..3825.416 rows=2 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999998
Planning time: 0.087 ms
Execution time: 3825.448 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.058..3909.013 rows=2 loops=1)
Filter: ((properties ->> 'color'::text) = 'blue'::text)
Rows Removed by Filter: 9999998
Planning time: 0.074 ms
Execution time: 3909.038 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.130..0.133 rows=2 loops=1)
Recheck Cond: (properties @> '{"color": "blue"}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.116..0.116 rows=2 loops=1)
Index Cond: (properties @> '{"color": "blue"}'::jsonb)
Planning time: 0.639 ms
Execution time: 0.176 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.095..0.098 rows=2 loops=1)
Recheck Cond: (properties @> '{"color": "blue"}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.085..0.085 rows=2 loops=1)
Index Cond: (properties @> '{"color": "blue"}'::jsonb)
Planning time: 0.065 ms
Execution time: 0.122 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.073..0.076 rows=2 loops=1)
Recheck Cond: (properties @> '{"color": "blue"}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.066..0.066 rows=2 loops=1)
Index Cond: (properties @> '{"color": "blue"}'::jsonb)
Planning time: 0.035 ms
Execution time: 0.090 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.091..0.095 rows=2 loops=1)
Recheck Cond: (properties @> '{"color": "blue"}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.079..0.079 rows=2 loops=1)
Index Cond: (properties @> '{"color": "blue"}'::jsonb)
Planning time: 0.033 ms
Execution time: 0.115 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.628..3747.784 rows=2 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999998
Planning time: 0.043 ms
Execution time: 3747.803 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.450..3675.520 rows=2 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999998
Planning time: 0.069 ms
Execution time: 3675.547 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.455..3651.867 rows=2 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999998
Planning time: 0.073 ms
Execution time: 3651.892 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on entity_jsonb (cost=0.00..382455.24 rows=50000 width=14) (actual time=0.479..3727.891 rows=2 loops=1)
Filter: ((properties ->> 'hassomething'::text) = 'false'::text)
Rows Removed by Filter: 9999998
Planning time: 0.070 ms
Execution time: 3727.918 ms
(5 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.146..0.150 rows=2 loops=1)
Recheck Cond: (properties @> '{"hassomething": false}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.131..0.131 rows=2 loops=1)
Index Cond: (properties @> '{"hassomething": false}'::jsonb)
Planning time: 0.095 ms
Execution time: 0.186 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.073..0.076 rows=2 loops=1)
Recheck Cond: (properties @> '{"hassomething": false}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.066..0.066 rows=2 loops=1)
Index Cond: (properties @> '{"hassomething": false}'::jsonb)
Planning time: 0.034 ms
Execution time: 0.090 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.082..0.084 rows=2 loops=1)
Recheck Cond: (properties @> '{"hassomething": false}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.075..0.075 rows=2 loops=1)
Index Cond: (properties @> '{"hassomething": false}'::jsonb)
Planning time: 0.047 ms
Execution time: 0.103 ms
(7 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on entity_jsonb (cost=7169.50..40427.16 rows=10000 width=14) (actual time=0.092..0.095 rows=2 loops=1)
Recheck Cond: (properties @> '{"hassomething": false}'::jsonb)
Heap Blocks: exact=2
-> Bitmap Index Scan on entity_jsonb_properties_idx (cost=0.00..7167.00 rows=10000 width=0) (actual time=0.085..0.085 rows=2 loops=1)
Index Cond: (properties @> '{"hassomething": false}'::jsonb)
Planning time: 0.033 ms
Execution time: 0.110 ms
(7 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.343..3549.098 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.335..3549.064 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.004..0.009 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.325..3549.045 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.010..0.011 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.919 ms
Execution time: 3549.132 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.170..2366.490 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.160..2366.452 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.006..0.010 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.151..2366.435 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.011..0.012 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.840 ms
Execution time: 2366.541 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.057..2342.796 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.051..2342.759 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.045..2342.745 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.011..0.011 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.532 ms
Execution time: 2342.836 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.055..2422.929 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.051..2422.895 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=1)
Filter: (name = 'color'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.043..2422.878 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'blue'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.011..0.011 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.541 ms
Execution time: 2422.970 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.597..3550.843 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.580..3550.689 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.014 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.572..3550.665 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'false'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.060..0.060 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.580 ms
Execution time: 3550.898 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.412..2555.255 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.406..2555.220 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.394..2555.200 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'false'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.011..0.011 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.697 ms
Execution time: 2555.306 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.328..2358.858 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.322..2358.822 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.006..0.009 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.314..2358.806 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'false'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.010..0.011 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.642 ms
Execution time: 2358.897 ms
(13 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.00..369527.46 rows=1 width=14) (actual time=0.315..2429.000 rows=2 loops=1)
-> Nested Loop (cost=0.56..369519.00 rows=1 width=4) (actual time=0.310..2428.956 rows=2 loops=1)
-> Seq Scan on entity_attribute ea (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=1)
Filter: (name = 'hassomething'::text)
Rows Removed by Filter: 4
-> Index Scan using entity_attribute_value_entity_attribute_id_idx on entity_attribute_value eav (cost=0.56..369517.92 rows=1 width=8) (actual time=0.302..2428.939 rows=2 loops=1)
Index Cond: (entity_attribute_id = ea.id)
Filter: (value = 'false'::text)
Rows Removed by Filter: 9999998
-> Index Scan using entity_pkey on entity e (cost=0.43..8.45 rows=1 width=18) (actual time=0.016..0.016 rows=1 loops=2)
Index Cond: (id = eav.entity_id)
Planning time: 0.574 ms
Execution time: 2429.039 ms
(13 rows)
@igrishaev
Copy link

Backref: https://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment