Re: issue with double ordering in a wrapped distinct - Mailing list pgsql-general
| From | Jonathan Vanasco |
|---|---|
| Subject | Re: issue with double ordering in a wrapped distinct |
| Date | |
| Msg-id | D1576B60-B331-4555-AAFA-8CC8CF6E8CB7@2xlp.com Whole thread Raw |
| In response to | Re: issue with double ordering in a wrapped distinct (Jonathan Vanasco <postgres@2xlp.com>) |
| List | pgsql-general |
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)
pgsql-general by date: