Search Postgresql Archives

Re: issue with double ordering in a wrapped distinct

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux