Quick Links

Re: issue with double ordering in a wrapped distinct

From:	Jonathan Vanasco <postgres(at)2xlp(dot)com>
To:	PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject:	Re: issue with double ordering in a wrapped distinct
Date:	2014-11-19 16:54:33
Message-ID:	D1576B60-B331-4555-AAFA-8CC8CF6E8CB7@2xlp.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	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 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

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

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

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

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

In response to

Re: issue with double ordering in a wrapped distinct at 2014-11-19 01:12:10 from Jonathan Vanasco

Browse pgsql-general by date

	From	Date	Subject
Next Message	Jeff Janes	2014-11-19 17:31:50	Re: [general] Error while decrypting using pgp
Previous Message	Anil Menon	2014-11-19 16:26:23	Performance question