I re-ran the query in multiple forms, and included it below (I regexed it to
become 'foo2bar' so it's more generic to others).
I also uploaded it as a public spreadsheet to google, because I think that is a
bit easier to look at:
https://docs.google.com/spreadsheets/d/1w9HM8w9YUpul5Bmy-uvzA4I6x5OFwWzR6K5atFG2ubw/edit?usp=sharing
The most interesting thing to me was how the planner was so drastically
affected by interplay of distinct and order in a subselect :
293 - SELECT ( SELECT DISTINCT ) ORDER LIMIT OFFSET
293 - SELECT DISTINCT ( SELECT DISTINCT ORDER ) LIMIT OFFSET
300 - SELECT DISTINCT ( SELECT ORDER ) LIMIT OFFSET
6400 - SELECT( SELECT DISTINCT ORDER ) LIMIT OFFSET
7631 - SELECT DISTINCT ( SELECT ) ORDER LIMIT OFFSET
And you can also see how the planner completely changed the strategy when
LIMIT/OFFSET was introduced to the first query --
394 SELECT ORDER BY;
446501 SELECT ORDER BY LIMIT OFFSET;
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A
EXPLAIN ANALYZE
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=48810.15..48842.34 rows=6437 width=4) (actual
time=283.850..389.587 rows=3468 loops=1)
-> Sort (cost=48810.15..48826.25 rows=6437 width=4) (actual
time=283.846..335.532 rows=44985 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: quicksort Memory: 3645kB
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4) (actual
time=2.734..221.878 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32 rows=7679
width=4) (actual time=2.718..27.531 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE) AND
(is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on idx__foo__attribute_id_a
(cost=0.00..320.17 rows=7966 width=0) (actual time=1.335..1.335 rows=8161
loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on foo_2_bar
(cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.010 rows=6 loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 394.606 ms
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED -- same as above, just adds a LIMIT/OFFSET
EXPLAIN ANALYZE
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
LIMIT 50
OFFSET 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.85..15386.21 rows=50 width=4) (actual
time=57698.794..446500.933 rows=50 loops=1)
-> Unique (cost=0.85..1980710.86 rows=6437 width=4) (actual
time=57698.789..446500.787 rows=50 loops=1)
-> Nested Loop (cost=0.85..1980694.77 rows=6437 width=4) (actual
time=57698.784..446498.319 rows=2011 loops=1)
-> Index Scan using idx__foo2bar__bar_id on foo_2_bar
(cost=0.43..75725.91 rows=1517741 width=8) (actual time=0.017..10373.409
rows=364872 loops=1)
-> Index Scan using idx__foo__filter1_a on foo
(cost=0.43..1.25 rows=1 width=4) (actual time=1.192..1.192 rows=0 loops=364872)
Index Cond: (id = foo_2_bar.foo_id)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE) AND
(is_c IS NOT TRUE) AND (is_d IS NOT TRUE) AND (attribute_id_a = 582))
Rows Removed by Filter: 1
Total runtime: 446501.050 ms
(9 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-DOUBLEORDER -- That sucked. Let's try wrapping Query
A in a subselect
EXPLAIN ANALYZE
SELECT DISTINCT qinner.bar_id FROM
(
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) qinner
ORDER BY qinner.bar_id ASC
LIMIT 50
OFFSET 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=48810.15..48811.03 rows=50 width=4) (actual time=286.674..291.183
rows=50 loops=1)
-> Unique (cost=48810.15..48922.80 rows=6437 width=4) (actual
time=286.671..291.082 rows=50 loops=1)
-> Unique (cost=48810.15..48842.34 rows=6437 width=4) (actual
time=286.668..290.977 rows=50 loops=1)
-> Sort (cost=48810.15..48826.25 rows=6437 width=4) (actual
time=286.664..288.812 rows=2011 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: quicksort Memory: 3645kB
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4)
(actual time=2.699..224.734 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32
rows=7679 width=4) (actual time=2.682..28.245 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT
TRUE) AND (is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on
idx__foo__attribute_id_a (cost=0.00..320.17 rows=7966 width=0) (actual
time=1.329..1.329 rows=8161 loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on
foo_2_bar (cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.011 rows=6
loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 293.399 ms
(17 rows)
Query A-LIMITED-SUBSELECT-DOUBLEORDER -- Does it matter where the distinct is?
EXPLAIN ANALYZE
SELECT DISTINCT qinner.bar_id FROM
(
SELECT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) qinner
ORDER BY qinner.bar_id ASC
LIMIT 50
OFFSET 0
;
-- Total runtime: 291.729 ms
EXPLAIN ANALYZE
SELECT qinner.bar_id FROM
(
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) qinner
ORDER BY qinner.bar_id ASC
LIMIT 50
OFFSET 0
;
-- Total runtime: 296.966 ms
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-OUTERORDER
EXPLAIN ANALYZE
SELECT DISTINCT qinner.bar_id FROM
(
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
) qinner
ORDER BY qinner.bar_id ASC
LIMIT 50
OFFSET 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=48842.07..48842.20 rows=50 width=4) (actual time=303.474..303.634
rows=50 loops=1)
-> Sort (cost=48842.07..48858.16 rows=6437 width=4) (actual
time=303.471..303.520 rows=50 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: top-N heapsort Memory: 27kB
-> HashAggregate (cost=48563.87..48628.24 rows=6437 width=4) (actual
time=295.615..299.590 rows=3468 loops=1)
-> HashAggregate (cost=48419.04..48483.41 rows=6437 width=4)
(actual time=287.433..291.489 rows=3468 loops=1)
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4)
(actual time=3.192..230.177 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32
rows=7679 width=4) (actual time=3.174..29.242 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT
TRUE) AND (is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on
idx__foo__attribute_id_a (cost=0.00..320.17 rows=7966 width=0) (actual
time=1.695..1.695 rows=8161 loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on
foo_2_bar (cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.011 rows=6
loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 303.766 ms
(17 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-OUTERORDER - Variation -- distinct on outer only
EXPLAIN ANALYZE
SELECT DISTINCT qinner.bar_id FROM
(
SELECT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
) qinner
ORDER BY qinner.bar_id ASC
LIMIT 50
OFFSET 0
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.85..15386.24 rows=50 width=4) (actual time=201.565..7631.099
rows=50 loops=1)
-> Unique (cost=0.85..1980714.86 rows=6437 width=4) (actual
time=201.562..7630.960 rows=50 loops=1)
-> Nested Loop (cost=0.85..1980698.77 rows=6437 width=4) (actual
time=201.557..7628.555 rows=2011 loops=1)
-> Index Scan using idx__foo2bar__bar_id on foo_2_bar
(cost=0.43..75725.91 rows=1517741 width=8) (actual time=0.034..748.009
rows=364872 loops=1)
-> Index Scan using idx__foo__filter1_a on foo
(cost=0.43..1.25 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=364872)
Index Cond: (id = foo_2_bar.foo_id)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE) AND
(is_c IS NOT TRUE) AND (is_d IS NOT TRUE) AND (attribute_id_a = 582))
Rows Removed by Filter: 1
Total runtime: 7631.194 ms
(9 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-OUTERORDER - Variation -- distinct on inner only
EXPLAIN ANALYZE
SELECT qinner.bar_id FROM
(
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
) qinner
ORDER BY qinner.bar_id ASC
LIMIT 50
OFFSET 0
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=48761.61..48761.73 rows=50 width=4) (actual time=293.188..293.334
rows=50 loops=1)
-> Sort (cost=48761.61..48777.70 rows=6437 width=4) (actual
time=293.185..293.234 rows=50 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: top-N heapsort Memory: 27kB
-> HashAggregate (cost=48419.04..48483.41 rows=6437 width=4) (actual
time=285.202..289.167 rows=3468 loops=1)
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4)
(actual time=3.022..228.559 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32
rows=7679 width=4) (actual time=2.999..29.461 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE)
AND (is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on idx__foo__attribute_id_a
(cost=0.00..320.17 rows=7966 width=0) (actual time=1.325..1.325 rows=8161
loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on foo_2_bar
(cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.011 rows=6 loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 293.452 ms
(16 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-INNERORDER
EXPLAIN ANALYZE
SELECT DISTINCT qinner.bar_id FROM
(
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) qinner
LIMIT 50
OFFSET 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=48810.15..48811.03 rows=50 width=4) (actual time=287.143..291.677
rows=50 loops=1)
-> Unique (cost=48810.15..48922.80 rows=6437 width=4) (actual
time=287.140..291.575 rows=50 loops=1)
-> Unique (cost=48810.15..48842.34 rows=6437 width=4) (actual
time=287.137..291.469 rows=50 loops=1)
-> Sort (cost=48810.15..48826.25 rows=6437 width=4) (actual
time=287.133..289.328 rows=2011 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: quicksort Memory: 3645kB
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4)
(actual time=2.713..225.116 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32
rows=7679 width=4) (actual time=2.696..28.539 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT
TRUE) AND (is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on
idx__foo__attribute_id_a (cost=0.00..320.17 rows=7966 width=0) (actual
time=1.325..1.325 rows=8161 loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on
foo_2_bar (cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.011 rows=6
loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 293.309 ms
(17 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-INNERORDER - variation - outer distinct
EXPLAIN ANALYZE
SELECT DISTINCT qinner.bar_id FROM
(
SELECT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) qinner
LIMIT 50
OFFSET 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=48810.15..48810.90 rows=50 width=4) (actual time=293.422..297.896
rows=50 loops=1)
-> Unique (cost=48810.15..48906.71 rows=6437 width=4) (actual
time=293.417..297.792 rows=50 loops=1)
-> Sort (cost=48810.15..48826.25 rows=6437 width=4) (actual
time=293.414..295.580 rows=2011 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: quicksort Memory: 3645kB
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4)
(actual time=2.706..231.424 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32
rows=7679 width=4) (actual time=2.690..30.373 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE)
AND (is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on idx__foo__attribute_id_a
(cost=0.00..320.17 rows=7966 width=0) (actual time=1.316..1.316 rows=8161
loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on foo_2_bar
(cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.011 rows=6 loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 299.542 ms
(16 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query A-LIMITED-SUBSELECT-INNERORDER - variation - inner distinct
EXPLAIN ANALYZE
SELECT qinner.bar_id FROM
(
SELECT DISTINCT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) qinner
LIMIT 50
OFFSET 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.85..15386.74 rows=50 width=4) (actual time=181.256..6408.615
rows=50 loops=1)
-> Unique (cost=0.85..1980714.86 rows=6437 width=4) (actual
time=181.252..6408.492 rows=50 loops=1)
-> Nested Loop (cost=0.85..1980698.77 rows=6437 width=4) (actual
time=181.248..6406.149 rows=2011 loops=1)
-> Index Scan using idx__foo2bar__bar_id on foo_2_bar
(cost=0.43..75725.91 rows=1517741 width=8) (actual time=0.031..768.898
rows=364872 loops=1)
-> Index Scan using idx__foo__filter1_a on foo
(cost=0.43..1.25 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=364872)
Index Cond: (id = foo_2_bar.foo_id)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE) AND
(is_c IS NOT TRUE) AND (is_d IS NOT TRUE) AND (attribute_id_a = 582))
Rows Removed by Filter: 1
Total runtime: 6408.725 ms
(9 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query B
EXPLAIN ANALYZE
SELECT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
GROUP BY foo_2_bar.bar_id
ORDER BY foo_2_bar.bar_id ASC
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=48810.15..48842.34 rows=6437 width=4) (actual
time=291.317..391.371 rows=3468 loops=1)
-> Sort (cost=48810.15..48826.25 rows=6437 width=4) (actual
time=291.310..340.108 rows=44985 loops=1)
Sort Key: foo_2_bar.bar_id
Sort Method: quicksort Memory: 3645kB
-> Nested Loop (cost=322.52..48402.94 rows=6437 width=4) (actual
time=3.183..229.189 rows=44985 loops=1)
-> Bitmap Heap Scan on foo (cost=322.09..27984.32 rows=7679
width=4) (actual time=3.165..29.759 rows=7885 loops=1)
Recheck Cond: (attribute_id_a = 582)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE) AND
(is_c IS NOT TRUE) AND (is_d IS NOT TRUE))
Rows Removed by Filter: 7
-> Bitmap Index Scan on idx__foo__attribute_id_a
(cost=0.00..320.17 rows=7966 width=0) (actual time=1.792..1.792 rows=8161
loops=1)
Index Cond: (attribute_id_a = 582)
-> Index Only Scan using idx__foo2bar__test on foo_2_bar
(cost=0.43..2.61 rows=5 width=8) (actual time=0.004..0.011 rows=6 loops=7885)
Index Cond: (foo_id = foo.id)
Heap Fetches: 0
Total runtime: 396.417 ms
(15 rows)
====================================================================================================================================================================
====================================================================================================================================================================
====================================================================================================================================================================
Query B-LIMITED
EXPLAIN ANALYZE
SELECT foo_2_bar.bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.attribute_id_a = 582
AND (foo.is_a IS NOT TRUE)
AND (foo.is_b IS NOT TRUE)
AND (foo.is_c IS NOT TRUE)
AND (foo.is_d IS NOT TRUE)
GROUP BY foo_2_bar.bar_id
ORDER BY foo_2_bar.bar_id ASC
LIMIT 50
OFFSET 0
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.85..15386.21 rows=50 width=4) (actual time=6317.604..223730.162
rows=50 loops=1)
-> Group (cost=0.85..1980710.86 rows=6437 width=4) (actual
time=6317.599..223730.023 rows=50 loops=1)
-> Nested Loop (cost=0.85..1980694.77 rows=6437 width=4) (actual
time=6317.595..223727.621 rows=2011 loops=1)
-> Index Scan using idx__foo2bar__bar_id on foo_2_bar
(cost=0.43..75725.91 rows=1517741 width=8) (actual time=0.018..3623.783
rows=364872 loops=1)
-> Index Scan using idx__foo__filter1_a on foo
(cost=0.43..1.25 rows=1 width=4) (actual time=0.601..0.601 rows=0 loops=364872)
Index Cond: (id = foo_2_bar.foo_id)
Filter: ((is_a IS NOT TRUE) AND (is_b IS NOT TRUE) AND
(is_c IS NOT TRUE) AND (is_d IS NOT TRUE) AND (attribute_id_a = 582))
Rows Removed by Filter: 1
Total runtime: 223730.277 ms
(9 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
