Clojurians Log v2

I'm not particularly well-versed in SQL, so I wonder looking at that query: 1. How efficient is all that nesting, and 2. How necessary is all that nesting (i.e. if you wrote the query by hand to get the same or better performance, would it look similar or be "flatter")

I tried running the query in the psql prompt today and the result a single json value that's almost the same as what is returned from the heql/query function.

So it's not doing a join between the actor and the film table and then reshaping the results into what's returned. It's actually getting Postgres to return the desired structure.

so I think the nesting is necessary to accomplish that feat

but it's not the approach you'd likely take if writing by hand

here's the query plan:

QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=447.63..447.64 rows=1 width=32)
   ->  Nested Loop  (cost=85.97..87.09 rows=5 width=17)
         ->  HashAggregate  (cost=85.82..85.87 rows=5 width=4)
               Group Key: "G__21777".actor_id
               ->  Nested Loop  (cost=4.32..85.81 rows=5 width=4)
                     ->  Seq Scan on film "G__21776"  (cost=0.00..67.50 rows=1 width=4)
                           Filter: ((title)::text ~~ '%LIFE%'::text)
                     ->  Bitmap Heap Scan on film_actor "G__21777"  (cost=4.32..18.26 rows=5 width=8)
                           Recheck Cond: ("G__21776".film_id = film_id)
                           ->  Bitmap Index Scan on idx_fk_film_id  (cost=0.00..4.32 rows=5 width=0)
                                 Index Cond: (film_id = "G__21776".film_id)
         ->  Index Scan using actor_pkey on actor "G__21772"  (cost=0.14..0.24 rows=1 width=17)
               Index Cond: (actor_id = "G__21777".actor_id)
   SubPlan 1
     ->  Aggregate  (cost=72.09..72.10 rows=1 width=32)
           ->  Nested Loop  (cost=0.28..72.09 rows=1 width=390)
                 ->  Seq Scan on film "G__21773"  (cost=0.00..67.50 rows=1 width=390)
                       Filter: ((title)::text ~~ '%LIFE%'::text)
                 ->  Index Only Scan using film_actor_pkey on film_actor "G__21774"  (cost=0.28..4.30 rows=1 width=4)
                       Index Cond: ((actor_id = "G__21772".actor_id) AND (film_id = "G__21773".film_id))
(20 rows)

Time: 1.105 ms

(not that I know how to read it or anything)

Interesting... Thanks 👍🏻

WITH "rs" AS
    (SELECT "G__19714"."first_name" AS "actor/first-name",
            "G__19714"."last_name" AS "actor/last-name",
            (COALESCE (
                         (SELECT JSON_AGG("G__19717".*)
                          FROM
                            (SELECT "G__19715"."fulltext" AS "film/fulltext",
                                    "G__19715"."release_year" AS "film/release-year",
                                    "G__19715"."replacement_cost" AS "film/replacement-cost",
                                    "G__19715"."title" AS "film/title",
                                    "G__19715"."language_id" AS "film/language-id",
                                    "G__19715"."rental_duration" AS "film/rental-duration",
                                    "G__19715"."description" AS "film/description",
                                    "G__19715"."film_id" AS "film/film-id",
                                    "G__19715"."special_features" AS "film/special-features",
                                    "G__19715"."original_language_id" AS "film/original-language-id",
                                    "G__19715"."length" AS "film/length",
                                    "G__19715"."rating" AS "film/rating",
                                    "G__19715"."rental_rate" AS "film/rental-rate",
                                    "G__19715"."last_update" AS "film/last-update"
                             FROM "film" AS "G__19715",
                                  "film_actor" AS "G__19716"
                             WHERE ("G__19714"."actor_id" = "G__19716"."actor_id")
                               AND ("G__19716"."film_id" = "G__19715"."film_id")
                               AND ("G__19715"."title" LIKE ?)) AS "G__19717"),'[]')) AS "actor/films"
     FROM "actor" AS "G__19714"
     WHERE EXISTS
         (SELECT ?
          FROM "film" AS "G__19718",
               "film_actor" AS "G__19719"
          WHERE ("G__19714"."actor_id" = "G__19719"."actor_id")
            AND ("G__19719"."film_id" = "G__19718"."film_id")
            AND ("G__19718"."title" LIKE ?)))
  SELECT COALESCE (json_agg("rs"),
                   '[]')::CHARACTER varying AS RESULT
  FROM
    (SELECT *
     FROM "rs") AS "rs"

Quite a beaut, eh?