Normal.md

Query:

SELECT predictions.site_id, SUM("shopper_orders"."total") AS sum_id FROM "predictions" INNER JOIN "shopper_orders" ON "shopper_orders"."uuid" = "predictions"."uuid" WHERE (predicted_at BETWEEN '2013-11-15 07:00:00' AND '2013-11-16 06:59:59') AND (control_group = 1) AND (shopper_orders.order_created_at > predictions.matched_at AND shopper_orders.order_created_at < (dateadd(h,24,predictions.matched_at)) AND (shopper_orders.cart_token = predictions.cart_token OR predictions.cart_token IS NULL OR predictions.cart_token = '')) GROUP BY predictions.site_id;

Explain:

Run time: 381ms

XN HashAggregate  (cost=0.65..0.66 rows=1 width=17)
  ->  XN Hash Join DS_DIST_NONE  (cost=0.18..0.65 rows=1 width=17)
        ->  XN Seq Scan on shopper_orders  (cost=0.00..0.20 rows=20 width=1053)
        ->  XN Hash  (cost=0.18..0.18 rows=1 width=1044)
              ->  XN Seq Scan on predictions  (cost=0.00..0.18 rows=1 width=1044)

granify=# select * from svl_query_summary where query = 7961 order by stm, seg, step;
 userid | query | stm | seg | step | maxtime | avgtime |  rows  |  bytes   | rate_row | rate_byte |        label         | is_diskbased |  workmem   | is_rrscan | is_delayed_scan 
--------+-------+-----+-----+------+---------+---------+--------+----------+----------+-----------+----------------------+--------------+------------+-----------+-----------------
    100 |  7961 |   0 |   0 |    0 |  175063 |  171224 |    302 |    27760 |          |           | scan   tbl=100322    | f            |          0 | t         | f
    100 |  7961 |   0 |   0 |    3 |  175063 |  171224 |    302 |    26552 |          |           | hash   tbl=240       | f            | 1972371456 | f         | f
    100 |  7961 |   1 |   1 |    0 |   94227 |   67518 | 435913 | 46918529 |          |           | scan   tbl=100352    | f            |          0 | t         | f
    100 |  7961 |   1 |   1 |    3 |   94227 |   67518 |      6 |        0 |          |           | hjoin  tbl=240       | f            |          0 | f         | f
    100 |  7961 |   1 |   1 |    6 |   94227 |   67518 |      5 |      160 |          |           | aggr   tbl=249       | f            |  379846656 | f         | f
    100 |  7961 |   1 |   1 |    7 |   94227 |   67518 |      5 |        0 |          |           | dist                 | f            |          0 | f         | f
    100 |  7961 |   1 |   2 |    0 |  121731 |  115155 |      5 |      160 |          |           | scan   tbl=16890     | f            |          0 | f         | f
    100 |  7961 |   1 |   2 |    1 |  121731 |  115155 |      2 |       64 |          |           | aggr   tbl=252       | f            | 1519386624 | f         | f
    100 |  7961 |   1 |   2 |    4 |  121731 |  115155 |      2 |        0 |          |           | return               | f            |          0 | f         | f
    100 |  7961 |   1 |   3 |    0 |   91226 |   91226 |      2 |       64 |          |           | scan   tbl=16891     | f            |          0 | f         | f
    100 |  7961 |   1 |   3 |    1 |   91226 |   91226 |      0 |        0 |          |           | return               | f            |          0 | f         | f
(11 rows)

granify=# select * from stl_scan where query = 7961 order by segment, slice;
 userid | query | slice | segment | step |         starttime          |          endtime           | tasknum |  rows  |  bytes   | fetches | type |  tbl   | is_rrscan | is_delayed_scan | rows_pre_filter 
--------+-------+-------+---------+------+----------------------------+----------------------------+---------+--------+----------+---------+------+--------+-----------+-----------------+-----------------
    100 |  7961 |     0 |       0 |    0 | 2013-11-15 20:58:04.741324 | 2013-11-15 20:58:04.916387 |      20 |     76 |     6904 |  950089 |    2 | 100322 | t         | f               |          950089
    100 |  7961 |     1 |       0 |    0 | 2013-11-15 20:58:04.741586 | 2013-11-15 20:58:04.913428 |      21 |     81 |     7524 |  992834 |    2 | 100322 | t         | f               |          992834
    100 |  7961 |     2 |       0 |    0 | 2013-11-15 20:58:04.752779 | 2013-11-15 20:58:04.919293 |      20 |     73 |     6780 | 1041608 |    2 | 100322 | t         | f               |         1041608
    100 |  7961 |     3 |       0 |    0 | 2013-11-15 20:58:04.753048 | 2013-11-15 20:58:04.924528 |      21 |     72 |     6552 |  965144 |    2 | 100322 | t         | f               |          965144
    100 |  7961 |     0 |       1 |    0 | 2013-11-15 20:58:04.986358 | 2013-11-15 20:58:05.040483 |      22 |  94007 | 10633392 |       0 |    2 | 100352 | t         | f               |          845755
    100 |  7961 |     1 |       1 |    0 | 2013-11-15 20:58:04.987398 | 2013-11-15 20:58:05.047825 |      23 |  93787 | 10607772 |       0 |    2 | 100352 | t         | f               |          843674
    100 |  7961 |     2 |       1 |    0 | 2013-11-15 20:58:04.98885  | 2013-11-15 20:58:05.083077 |      22 | 153964 | 15029139 |       0 |    2 | 100352 | t         | f               |         1384528
    100 |  7961 |     3 |       1 |    0 | 2013-11-15 20:58:04.993495 | 2013-11-15 20:58:05.054791 |      23 |  94155 | 10648226 |       0 |    2 | 100352 | t         | f               |          847057
    100 |  7961 |     0 |       2 |    0 | 2013-11-15 20:58:04.963868 | 2013-11-15 20:58:05.085599 |      20 |      3 |       96 |       0 |    1 |  16890 | f         | f               |               0
    100 |  7961 |     1 |       2 |    0 | 2013-11-15 20:58:04.964241 | 2013-11-15 20:58:05.085545 |      21 |      0 |        0 |       0 |    1 |  16890 | f         | f               |               0
    100 |  7961 |     2 |       2 |    0 | 2013-11-15 20:58:04.9755   | 2013-11-15 20:58:05.084183 |      20 |      0 |        0 |       0 |    1 |  16890 | f         | f               |               0
    100 |  7961 |     3 |       2 |    0 | 2013-11-15 20:58:04.975776 | 2013-11-15 20:58:05.084679 |      21 |      2 |       64 |       0 |    1 |  16890 | f         | f               |               0
    100 |  7961 |  3211 |       3 |    0 | 2013-11-15 20:58:04.993519 | 2013-11-15 20:58:05.084745 |       0 |      2 |       64 |       0 |    1 |  16891 | f         | f               |               0
(13 rows)

Run time: 4.31s XN HashAggregate (cost=74396.25..74396.25 rows=1 width=14) -> XN Hash Join DS_DIST_NONE (cost=64865.47..74396.24 rows=1 width=14) -> XN Seq Scan on shopper_orders (cost=0.00..4235.88 rows=423588 width=94) -> XN Hash (cost=64865.47..64865.47 rows=1 width=73) -> XN Seq Scan on predictions (cost=0.00..64865.47 rows=1 width=73)

granify=# select * from svl_query_summary where query = 99959 order by stm, seg, step; userid | query | stm | seg | step | maxtime | avgtime | rows | bytes | rate_row | rate_byte | label | is_diskbased | workmem | is_rrscan | is_delayed_scan --------+-------+-----+-----+------+---------+---------+--------+----------+----------+-----------+----------------------+--------------+------------+-----------+----------------- 100 | 99959 | 0 | 0 | 0 | 80947 | 62023 | 180 | 16412 | | | scan tbl=102138 | f | 0 | t | f 100 | 99959 | 0 | 0 | 3 | 80947 | 62023 | 180 | 15692 | | | hash tbl=240 | f | 1972371456 | f | f 100 | 99959 | 1 | 1 | 0 | 4082748 | 3229614 | 435556 | 46882183 | 108889 | 11720545 | scan tbl=102142 | f | 0 | t | f 100 | 99959 | 1 | 1 | 3 | 4082748 | 3229614 | 1 | 0 | 0 | 0 | hjoin tbl=240 | f | 0 | f | f 100 | 99959 | 1 | 1 | 6 | 4082748 | 3229614 | 1 | 32 | 0 | 8 | aggr tbl=249 | f | 380633088 | f | f 100 | 99959 | 1 | 1 | 7 | 4082748 | 3229614 | 1 | 0 | 0 | 0 | dist | f | 0 | f | f 100 | 99959 | 1 | 2 | 0 | 4105940 | 4102233 | 1 | 32 | 0 | 8 | scan tbl=6910 | f | 0 | f | f 100 | 99959 | 1 | 2 | 1 | 4105940 | 4102233 | 1 | 32 | 0 | 8 | aggr tbl=252 | f | 1522532352 | f | f 100 | 99959 | 1 | 2 | 4 | 4105940 | 4102233 | 1 | 0 | 0 | 0 | return | f | 0 | f | f 100 | 99959 | 1 | 3 | 0 | 4084017 | 4084017 | 1 | 32 | 0 | 8 | scan tbl=6911 | f | 0 | f | f 100 | 99959 | 1 | 3 | 1 | 4084017 | 4084017 | 0 | 0 | 0 | 0 | return | f | 0 | f | f (11 rows)

granify=# select * from stl_scan where query = 99959 order by segment, slice; userid | query | slice | segment | step | starttime | endtime | tasknum | rows | bytes | fetches | type | tbl | is_rrscan | is_delayed_scan | rows_pre_filter --------+-------+-------+---------+------+----------------------------+----------------------------+---------+--------+----------+---------+------+--------+-----------+-----------------+----------------- 100 | 99959 | 0 | 0 | 0 | 2013-11-15 20:57:59.664009 | 2013-11-15 20:57:59.721489 | 20 | 38 | 3416 | 165942 | 2 | 102138 | t | f | 165942 100 | 99959 | 1 | 0 | 0 | 2013-11-15 20:57:59.687131 | 2013-11-15 20:57:59.744813 | 21 | 51 | 4704 | 171754 | 2 | 102138 | t | f | 171754 100 | 99959 | 2 | 0 | 0 | 2013-11-15 20:57:59.674656 | 2013-11-15 20:57:59.755603 | 20 | 42 | 3872 | 161764 | 2 | 102138 | t | f | 161764 100 | 99959 | 3 | 0 | 0 | 2013-11-15 20:57:59.681684 | 2013-11-15 20:57:59.73367 | 21 | 49 | 4420 | 168468 | 2 | 102138 | t | f | 168468 100 | 99959 | 0 | 1 | 0 | 2013-11-15 20:57:59.845634 | 2013-11-15 20:58:02.861507 | 22 | 93950 | 10626952 | 0 | 2 | 102142 | t | f | 8720138 100 | 99959 | 1 | 1 | 0 | 2013-11-15 20:57:59.845988 | 2013-11-15 20:58:02.79565 | 23 | 93722 | 10600453 | 0 | 2 | 102142 | t | f | 8697041 100 | 99959 | 2 | 1 | 0 | 2013-11-15 20:57:59.851426 | 2013-11-15 20:58:03.934174 | 22 | 153782 | 15012523 | 0 | 2 | 102142 | t | f | 14276791 100 | 99959 | 3 | 1 | 0 | 2013-11-15 20:57:59.851766 | 2013-11-15 20:58:02.721939 | 23 | 94102 | 10642255 | 0 | 2 | 102142 | t | f | 8730753 100 | 99959 | 0 | 2 | 0 | 2013-11-15 20:57:59.823692 | 2013-11-15 20:58:03.929632 | 20 | 1 | 32 | 0 | 1 | 6910 | f | f | 0 100 | 99959 | 1 | 2 | 0 | 2013-11-15 20:57:59.823867 | 2013-11-15 20:58:03.929668 | 21 | 0 | 0 | 0 | 1 | 6910 | f | f | 0 100 | 99959 | 2 | 2 | 0 | 2013-11-15 20:57:59.842195 | 2013-11-15 20:58:03.935169 | 20 | 0 | 0 | 0 | 1 | 6910 | f | f | 0 100 | 99959 | 3 | 2 | 0 | 2013-11-15 20:57:59.830984 | 2013-11-15 20:58:03.935204 | 21 | 0 | 0 | 0 | 1 | 6910 | f | f | 0 100 | 99959 | 3211 | 3 | 0 | 2013-11-15 20:57:59.849108 | 2013-11-15 20:58:03.933125 | 0 | 1 | 32 | 0 | 1 | 6911 | f | f | 0 (13 rows)

buger/Normal.md