CODEWITHSUNDEEP
sqlpostgresql
Tie-fighter
Tie-fighter

Reputation: 1073

Joining many-to-many relationships, execution time

I have more questions concerning Joining many-to-many relationships but thought it would be better to ask a new question.

Full query:

SELECT p.id
     ,(100 * sum((a.price > 0)::int)) / cc.ct AS commercial
     ,(100 * sum((a.price = 0)::int)) / cf.ct AS free
FROM  (SELECT count(*)::float AS ct FROM applications WHERE price > 0) AS cc
     ,(SELECT count(*)::float AS ct FROM applications WHERE price = 0) AS cf
     ,permissions p
LEFT   JOIN applications_permissions ap ON ap.permission_id = p.id
LEFT   JOIN applications a ON a.id = ap.application_id
GROUP  BY 1, cc.ct, cf.ct
ORDER  BY 2 DESC, 3 DESC, 1;

Concerning execution time:

SELECT p.id

-> Total runtime: 2,467.801 ms

SELECT (SELECT regex FROM permissions WHERE id = p.id) AS regex

-> Total runtime: 28,882.293 ms

What is happening here? What is the correct way to do this?

SELECT p.id

QUERY PLAN
Sort  (cost=123257.63..123258.07 rows=178 width=24)
  Sort Key: (((sum(((a.price > 0::double precision))::integer))::double precision / ((count(*))::double precision))), (((sum(((a.price = 0::double precision))::integer))::double precision / ((count(*))::double precision))), p.id
  ->  HashAggregate  (cost=123244.74..123250.97 rows=178 width=24)
        ->  Hash Left Join  (cost=53709.64..115103.31 rows=651315 width=24)
              Hash Cond: (ap.application_id = a.id)
              ->  Hash Left Join  (cost=42222.11..82232.48 rows=651315 width=24)
                    Hash Cond: (p.id = ap.permission_id)
                    ->  Nested Loop  (cost=18283.65..18305.06 rows=178 width=20)
                          ->  Index Scan using permissions_pkey on permissions p  (cost=0.00..17.85 rows=178 width=4)
                                Filter: (regex IS NOT NULL)
                          ->  Materialize  (cost=18283.65..18283.66 rows=1 width=16)
                                ->  Nested Loop  (cost=18283.60..18283.65 rows=1 width=16)
                                      ->  Aggregate  (cost=9016.51..9016.52 rows=1 width=0)
                                            ->  Seq Scan on applications  (cost=0.00..8915.85 rows=40262 width=0)
                                                  Filter: (price > 0::double precision)
                                      ->  Aggregate  (cost=9267.09..9267.10 rows=1 width=0)
                                            ->  Seq Scan on applications  (cost=0.00..8915.85 rows=140494 width=0)
                                                  Filter: (price = 0::double precision)
                    ->  Hash  (cost=11271.65..11271.65 rows=772065 width=8)
                          ->  Seq Scan on applications_permissions ap  (cost=0.00..11271.65 rows=772065 width=8)
              ->  Hash  (cost=8453.68..8453.68 rows=184868 width=8)
                    ->  Seq Scan on applications a  (cost=0.00..8453.68 rows=184868 width=8)
22 row(s)
Total runtime: 4.524 ms

SELECT (SELECT ...)

QUERY PLAN
Sort  (cost=3796049.42..3796049.86 rows=178 width=24)
  Sort Key: (((sum(((a.price > 0::double precision))::integer))::double precision / ((count(*))::double precision))), (((sum(((a.price = 0::double precision))::integer))::double precision / ((count(*))::double precision))), ((SubPlan 1))
  ->  HashAggregate  (cost=3795033.06..3796042.76 rows=178 width=24)
        ->  Hash Left Join  (cost=53709.64..3786891.62 rows=651315 width=24)
              Hash Cond: (ap.application_id = a.id)
              ->  Hash Left Join  (cost=42222.11..82232.48 rows=651315 width=24)
                    Hash Cond: (p.id = ap.permission_id)
                    ->  Nested Loop  (cost=18283.65..18305.06 rows=178 width=20)
                          ->  Index Scan using permissions_pkey on permissions p  (cost=0.00..17.85 rows=178 width=4)
                                Filter: (regex IS NOT NULL)
                          ->  Materialize  (cost=18283.65..18283.66 rows=1 width=16)
                                ->  Nested Loop  (cost=18283.60..18283.65 rows=1 width=16)
                                      ->  Aggregate  (cost=9016.51..9016.52 rows=1 width=0)
                                            ->  Seq Scan on applications  (cost=0.00..8915.85 rows=40262 width=0)
                                                  Filter: (price > 0::double precision)
                                      ->  Aggregate  (cost=9267.09..9267.10 rows=1 width=0)
                                            ->  Seq Scan on applications  (cost=0.00..8915.85 rows=140494 width=0)
                                                  Filter: (price = 0::double precision)
                    ->  Hash  (cost=11271.65..11271.65 rows=772065 width=8)
                          ->  Seq Scan on applications_permissions ap  (cost=0.00..11271.65 rows=772065 width=8)
              ->  Hash  (cost=8453.68..8453.68 rows=184868 width=8)
                    ->  Seq Scan on applications a  (cost=0.00..8453.68 rows=184868 width=8)
              SubPlan 1
                ->  Seq Scan on permissions  (cost=0.00..5.64 rows=1 width=26)
                      Filter: (id = $0)
25 row(s)
Total runtime: 6.566 ms

Upvotes: 3

Views: 151

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

Looks like you are trying to add a correlated subquery to the SELECT list, while you could just select the column:

SELECT p.id, p.regex
     ,(100 * sum((a.price > 0)::int)) / cc.ct AS commercial
     ,(100 * sum((a.price = 0)::int)) / cf.ct AS free
FROM  (SELECT count(*)::float AS ct FROM applications WHERE price > 0) AS cc
     ,(SELECT count(*)::float AS ct FROM applications WHERE price = 0) AS cf
     ,permissions p
LEFT   JOIN applications_permissions ap ON ap.permission_id = p.id
LEFT   JOIN applications a ON a.id = ap.application_id
GROUP  BY p.id, p.regex, cc.ct, cf.ct
ORDER  BY commercial DESC, free DESC, p.id;

If p.id is the primary key, it covers the whole table and you don't have to add p.regex in GROUP BY since Postgres 9.1.

Upvotes: 1

Related Questions