Skip to content

Instantly share code, notes, and snippets.

@ddrscott

ddrscott/sub_query_test.sql

Last active February 27, 2018 16:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ddrscott/9c8e1d1a8cc31f3087ea7cf8474af194 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ddrscott/9c8e1d1a8cc31f3087ea7cf8474af194 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ddrscott revised this gist Dec 22, 2017. 1 changed file with 49 additions and 2 deletions.
    51 changes: 49 additions & 2 deletions sub_query_test.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -18,7 +18,6 @@ FROM direct_delivery_items;
    Time: 19779.166 ms -- base line measurement



    anon=# SELECT
    SUM(impressions) + SUM(impressions)
    FROM direct_delivery_items;
    @@ -68,4 +67,52 @@ FROM
    1089285805864
    (1 row)

    Time: 39579.068 ms -- still running it twice
    Time: 39579.068 ms -- still running it twice




    /*************
    Join Subquery
    *************/

    anon=# SELECT * FROM generate_series(1, 2);
    generate_series
    -----------------
    1
    2
    (2 rows)

    Time: 0.240 ms


    anon=#
    anon=# SELECT
    anon-# t2.b
    anon-# FROM generate_series(1, 2)
    anon-# JOIN LATERAL (
    anon(# SELECT SUM(impressions) b FROM direct_delivery_items
    anon(# ) t2 ON true;
    b
    --------------
    544642902932
    544642902932
    (2 rows)

    Time: 19812.964 ms -- no penalty, SUM runs once


    anon=#
    anon=# SELECT
    anon-# t2.b
    anon-# FROM generate_series(1, 2)
    anon-# JOIN (
    anon(# SELECT SUM(impressions) b FROM direct_delivery_items
    anon(# ) t2 ON true;
    b
    --------------
    544642902932
    544642902932
    (2 rows)

    Time: 19543.773 ms -- no penalty, SUM runs once
  2. ddrscott revised this gist Dec 22, 2017. 1 changed file with 7 additions and 3 deletions.
    10 changes: 7 additions & 3 deletions sub_query_test.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,7 @@ anon-# FROM direct_delivery_items;
    544642902932
    (1 row)

    Time: 19853.941 ms
    Time: 19853.941 ms -- base line measurement
    anon=# SELECT
    SUM(impressions)
    FROM direct_delivery_items;
    @@ -15,7 +15,10 @@ FROM direct_delivery_items;
    544642902932
    (1 row)

    Time: 19779.166 ms
    Time: 19779.166 ms -- base line measurement



    anon=# SELECT
    SUM(impressions) + SUM(impressions)
    FROM direct_delivery_items;
    @@ -24,7 +27,8 @@ FROM direct_delivery_items;
    1089285805864
    (1 row)

    Time: 19611.613 ms -- single table scan
    Time: 19611.613 ms -- still single table scan



    anon=# SELECT
  3. ddrscott created this gist Dec 22, 2017.
    67 changes: 67 additions & 0 deletions sub_query_test.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,67 @@
    anon=# SELECT
    anon-# SUM(impressions)
    anon-# FROM direct_delivery_items;
    sum
    --------------
    544642902932
    (1 row)

    Time: 19853.941 ms
    anon=# SELECT
    SUM(impressions)
    FROM direct_delivery_items;
    sum
    --------------
    544642902932
    (1 row)

    Time: 19779.166 ms
    anon=# SELECT
    SUM(impressions) + SUM(impressions)
    FROM direct_delivery_items;
    ?column?
    ---------------
    1089285805864
    (1 row)

    Time: 19611.613 ms -- single table scan


    anon=# SELECT
    SUM(impressions) + SUM(impressions)
    FROM direct_delivery_items;
    ?column?
    ---------------
    1089285805864
    (1 row)

    Time: 19295.732 ms -- still single table scan



    anon=# SELECT
    anon-# a + b
    anon-# FROM
    anon-# (SELECT SUM(impressions) a FROM direct_delivery_items) t1,
    anon-# (SELECT SUM(impressions) b FROM direct_delivery_items) t2
    anon-# ;
    ?column?
    ---------------
    1089285805864
    (1 row)

    Time: 40890.009 ms -- seems like it runs it twice


    anon=# SELECT
    a + b
    FROM
    (SELECT SUM(impressions) a FROM direct_delivery_items) t1,
    (SELECT SUM(impressions) b FROM direct_delivery_items) t2
    ;
    ?column?
    ---------------
    1089285805864
    (1 row)

    Time: 39579.068 ms -- still running it twice