Thread: issue with double ordering in a wrapped distinct
I have a particular query that returns resultset of 45k rows out of a large resultset (pg 9.3 and 9.1)
It's a many 2 many query, where I"m trying to search for Bar based on attributes in a linked Foo.
I tweaked the indexes, optimized the query, and got it down an acceptable speed around 1,100ms
the second I added a limit/offset though -- the query plan completely changed and it ballooned up to 297,340 ms. Yes,
Iwaited that long to see what was going on in the query planner.
I did a lot of playing around, and managed to get this form of a query to work in 305ms with a limit/offset.
SELECT DISTINCT qinner.bar_id
FROM
(SELECT foo_2_bar.bar_id AS bar_id
FROM foo_2_bar
JOIN foo ON foo_2_bar.foo_id = foo.id
WHERE foo.biz_id = 1
AND (foo.is_hidden IS NOT TRUE)
ORDER BY foo_2_bar.bar_id ASC
) AS qinner
ORDER BY qinner.bar_id ASC
LIMIT 100
OFFSET 0
;
This is what I don't understand -- notice the two order_by calls.
If i run this with an inner and outer order_by, I get ~305ms. (I don't think I need both, but I wasn't sure if
orderingis kept from a subselect )
If i run this with only the inner, I get ~304ms.
If I run this with only the outer, it's pushing over 10minutes again
i'm wondering if anyone might know why that performance hit would be happening
Jonathan Vanasco <postgres@2xlp.com> writes: > This is what I don't understand -- notice the two order_by calls. > If i run this with an inner and outer order_by, I get ~305ms. (I don't think I need both, but I wasn't sure if orderingis kept from a subselect ) > If i run this with only the inner, I get ~304ms. > If I run this with only the outer, it's pushing over 10minutes again > i'm wondering if anyone might know why that performance hit would be happening The inner ORDER BY would prevent the planner from flattening the two query levels into one; but as for why it gets a much worse plan after flattening --- insufficient data. https://wiki.postgresql.org/wiki/Slow_Query_Questions regards, tom lane
Jonathan Vanasco-7 wrote > I have a particular query that returns resultset of 45k rows out of a > large resultset (pg 9.3 and 9.1) > > It's a many 2 many query, where I"m trying to search for Bar based on > attributes in a linked Foo. > > I tweaked the indexes, optimized the query, and got it down an acceptable > speed around 1,100ms > > the second I added a limit/offset though -- the query plan completely > changed and it ballooned up to 297,340 ms. Yes, I waited that long to > see what was going on in the query planner. > > I did a lot of playing around, and managed to get this form of a query to > work in 305ms with a limit/offset. > > SELECT DISTINCT qinner.bar_id > FROM > (SELECT foo_2_bar.bar_id AS bar_id > FROM foo_2_bar > JOIN foo ON foo_2_bar.foo_id = foo.id > WHERE foo.biz_id = 1 > AND (foo.is_hidden IS NOT TRUE) > ORDER BY foo_2_bar.bar_id ASC > ) AS qinner > ORDER BY qinner.bar_id ASC > LIMIT 100 > OFFSET 0 > ; > > This is what I don't understand -- notice the two order_by calls. > > If i run this with an inner and outer order_by, I get ~305ms. (I don't > think I need both, but I wasn't sure if ordering is kept from a subselect > ) > > If i run this with only the inner, I get ~304ms. > > If I run this with only the outer, it's pushing over 10minutes again > > i'm wondering if anyone might know why that performance hit would be > happening Pretty sure we need to see the explains, ideally with ANALYZE. The DISTINCT at the top-level will cause any ordering that the subquery imposes to be lost but because the subquery output is ordered the distinct likely runs more efficiently. I presume you have a reason for not simply doing away with the subquery altogether... The HashAgg used by DISTINCT is fairly expensive and apparently can be optimized away if it knows it is receiving sorted data from the subquery. Lacking that knowledge the entire subquery relation needs to be HashAgg'd before the LIMIT can be applied to its results. My uninformed question is whether DISTINCT can be made smart enough to realize that it would be cheaper to sort-and-scan instead of using HashAgg followed by sort... Then again without plans I am merely guessing here... David J. -- View this message in context: http://postgresql.nabble.com/issue-with-double-ordering-in-a-wrapped-distinct-tp5827443p5827446.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Nov 18, 2014, at 6:43 PM, Tom Lane wrote:
but as for why it gets a much worse plan after
flattening --- insufficient data.
Thanks. I'll run some test cases in the morning and post the full queries matched with ANALYZE EXPLAIN.
This is just puzzling to me. I was hoping there might be a more general planner issue that someone would have noticed.
On Nov 18, 2014, at 6:55 PM, David G Johnston wrote:
I presume you have a reason for not simply doing away with the subquery
altogether...
When not using the subquery, the query ran in 1s -- if I didn't have a LIMIT/OFFSET
Adding a LIMIT/OFFSET to that query made it run for nearly 6 minutes.
The only way I could manage to trick it to use the better query plan, was to wrap the "good" query as a subquery, and then run a LIMIT/OFFSET in the outer query.
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
asubselect :
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=7885loops=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.335rows=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.010rows=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)
(actualtime=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.192rows=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.245rows=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
NOTTRUE))
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)
(actualtime=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.242rows=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
NOTTRUE))
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)
(actualtime=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)
(actualtime=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.016rows=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=7885loops=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)
(actualtime=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)
(actualtime=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.539rows=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
NOTTRUE))
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)
(actualtime=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=7885loops=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)
(actualtime=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)
(actualtime=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)
(actualtime=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.013rows=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=7885loops=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.792rows=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.011rows=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)
(actualtime=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.601rows=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)