Skip to main content

why does running the database designer result in poorer performance?

  • January 4, 2014
  • 7 replies
  • 17 views
matthewcornell
Forum|alt.badge.img+1
Hi Folks,

I'm exploring Vertica performance, and I was surprised that a sample query in our small database ran faster *before* running the the database designer (1s and 4s respectively). I supplied a query file to the designer, but it has only one query (our little benchmark query), so maybe that confused it. I'm happy to provide any detail that might help. What I looked at so far is how the default projections that get created when I import data compare to the ones output by the designer (see #1 and #2 below).

A related question: How do I undo deploying the database designer changes? I can DROP PROJECTION on the created ones, but won't that leave the database with no super projections?

Version: Vertica Analytic Database v7.0.0-0

Thanks very much!


-- #1: Schema and default projections created by import:

CREATE TABLE public.acts_in
(
    movie_id int,
    actor_id int,
    number_of_movies int,
    acts_in_id int NOT NULL,
    actor_age int,
    id int
);


CREATE TABLE public.movie
(
    movie_id int NOT NULL,
    title varchar(100),
    ml_title varchar(100),
    year int,
    budget int,
    gross int,
    action int,
    comedy int,
    sci_fi int,
    thriller int,
    drama int,
    other int,
    critic_rating int,
    num_ratings int,
    id int
);


CREATE TABLE public.user_ratings
(
    users_id int,
    movie_id int,
    rating int,
    user_ratings_id int NOT NULL,
    id int
);


CREATE TABLE public.users
(
    users_id int NOT NULL,
    gender int,
    age_range int,
    occupation int,
    zipcode int,
    num_ratings int,
    id int
);


CREATE TABLE public.actor
(
    actor_id int NOT NULL,
    name varchar(60),
    month int,
    day int,
    year int,
    is_female int,
    id int
);



CREATE PROJECTION public.actor /*+createtype(L)*/
(
 actor_id,
 name,
 month,
 day,
 year,
 is_female,
 id
)
AS
 SELECT actor.actor_id,
        actor.name,
        actor.month,
        actor.day,
        actor.year,
        actor.is_female,
        actor.id
 FROM public.actor
 ORDER BY actor.actor_id,
          actor.name,
          actor.month,
          actor.day,
          actor.year,
          actor.is_female,
          actor.id
SEGMENTED BY hash(actor.actor_id, actor.month, actor.day, actor.year, actor.is_female, actor.id, actor.name) ALL NODES KSAFE 1;

CREATE PROJECTION public.movie /*+createtype(L)*/
(
 movie_id,
 title,
 ml_title,
 year,
 budget,
 gross,
 action,
 comedy,
 sci_fi,
 thriller,
 drama,
 other,
 critic_rating,
 num_ratings,
 id
)
AS
 SELECT movie.movie_id,
        movie.title,
        movie.ml_title,
        movie.year,
        movie.budget,
        movie.gross,
        movie.action,
        movie.comedy,
        movie.sci_fi,
        movie.thriller,
        movie.drama,
        movie.other,
        movie.critic_rating,
        movie.num_ratings,
        movie.id
 FROM public.movie
 ORDER BY movie.movie_id,
          movie.title,
          movie.ml_title,
          movie.year,
          movie.budget,
          movie.gross,
          movie.action,
          movie.comedy,
          movie.sci_fi,
          movie.thriller,
          movie.drama,
          movie.other,
          movie.critic_rating,
          movie.num_ratings,
          movie.id
SEGMENTED BY hash(movie.movie_id, movie.year, movie.budget, movie.gross, movie.action, movie.comedy, movie.sci_fi, movie.thriller, movie.drama, movie.other, movie.critic_rating, movie.num_ratings, movie.id, movie.title, movie.ml_title) ALL NODES KSAFE 1;

CREATE PROJECTION public.users /*+createtype(L)*/
(
 users_id,
 gender,
 age_range,
 occupation,
 zipcode,
 num_ratings,
 id
)
AS
 SELECT users.users_id,
        users.gender,
        users.age_range,
        users.occupation,
        users.zipcode,
        users.num_ratings,
        users.id
 FROM public.users
 ORDER BY users.users_id,
          users.gender,
          users.age_range,
          users.occupation,
          users.zipcode,
          users.num_ratings,
          users.id
SEGMENTED BY hash(users.users_id, users.gender, users.age_range, users.occupation, users.zipcode, users.num_ratings, users.id) ALL NODES KSAFE 1;

CREATE PROJECTION public.acts_in /*+createtype(L)*/
(
 movie_id,
 actor_id,
 number_of_movies,
 acts_in_id,
 actor_age,
 id
)
AS
 SELECT acts_in.movie_id,
        acts_in.actor_id,
        acts_in.number_of_movies,
        acts_in.acts_in_id,
        acts_in.actor_age,
        acts_in.id
 FROM public.acts_in
 ORDER BY acts_in.movie_id,
          acts_in.actor_id,
          acts_in.number_of_movies,
          acts_in.acts_in_id,
          acts_in.actor_age,
          acts_in.id
SEGMENTED BY hash(acts_in.movie_id, acts_in.actor_id, acts_in.number_of_movies, acts_in.acts_in_id, acts_in.actor_age, acts_in.id) ALL NODES KSAFE 1;

CREATE PROJECTION public.user_ratings /*+createtype(L)*/
(
 users_id,
 movie_id,
 rating,
 user_ratings_id,
 id
)
AS
 SELECT user_ratings.users_id,
        user_ratings.movie_id,
        user_ratings.rating,
        user_ratings.user_ratings_id,
        user_ratings.id
 FROM public.user_ratings
 ORDER BY user_ratings.users_id,
          user_ratings.movie_id,
          user_ratings.rating,
          user_ratings.user_ratings_id,
          user_ratings.id
SEGMENTED BY hash(user_ratings.users_id, user_ratings.movie_id, user_ratings.rating, user_ratings.user_ratings_id, user_ratings.id) ALL NODES KSAFE 1;


SELECT MARK_DESIGN_KSAFE(1);


-- #2: Projections deployed by the database designer:


CREATE PROJECTION actor_DBD_1_rep_movielens_design /*+createtype(D)*/
(
 actor_id ENCODING COMMONDELTA_COMP,
 name ENCODING AUTO,
 month ENCODING DELTAVAL,
 day ENCODING DELTAVAL,
 year ENCODING COMMONDELTA_COMP,
 is_female ENCODING BLOCKDICT_COMP,
 id ENCODING COMMONDELTA_COMP
)
AS
 SELECT actor_id,
        name,
        month,
        day,
        year,
        is_female,
        id
 FROM public.actor
 ORDER BY actor_id
UNSEGMENTED ALL NODES;

CREATE PROJECTION acts_in_DBD_2_rep_movielens_design /*+createtype(D)*/
(
 movie_id ENCODING DELTAVAL,
 actor_id ENCODING COMMONDELTA_COMP,
 number_of_movies ENCODING COMMONDELTA_COMP,
 acts_in_id ENCODING DELTAVAL,
 actor_age ENCODING COMMONDELTA_COMP,
 id ENCODING DELTAVAL
)
AS
 SELECT movie_id,
        actor_id,
        number_of_movies,
        acts_in_id,
        actor_age,
        id
 FROM public.acts_in
 ORDER BY actor_id
UNSEGMENTED ALL NODES;

CREATE PROJECTION movie_DBD_3_rep_movielens_design /*+createtype(D)*/
(
 movie_id ENCODING COMMONDELTA_COMP,
 title ENCODING AUTO,
 ml_title ENCODING AUTO,
 year ENCODING COMMONDELTA_COMP,
 budget ENCODING BLOCK_DICT,
 gross ENCODING DELTARANGE_COMP,
 action ENCODING BLOCKDICT_COMP,
 comedy ENCODING BLOCKDICT_COMP,
 sci_fi ENCODING BLOCKDICT_COMP,
 thriller ENCODING BLOCKDICT_COMP,
 drama ENCODING BLOCKDICT_COMP,
 other ENCODING BLOCKDICT_COMP,
 critic_rating ENCODING DELTAVAL,
 num_ratings ENCODING DELTAVAL,
 id ENCODING COMMONDELTA_COMP
)
AS
 SELECT movie_id,
        title,
        ml_title,
        year,
        budget,
        gross,
        action,
        comedy,
        sci_fi,
        thriller,
        drama,
        other,
        critic_rating,
        num_ratings,
        id
 FROM public.movie
 ORDER BY movie_id
UNSEGMENTED ALL NODES;

CREATE PROJECTION user_ratings_DBD_4_rep_movielens_design /*+createtype(D)*/
(
 users_id ENCODING DELTARANGE_COMP,
 movie_id ENCODING RLE,
 rating ENCODING RLE,
 user_ratings_id ENCODING COMMONDELTA_COMP,
 id ENCODING COMMONDELTA_COMP
)
AS
 SELECT users_id,
        movie_id,
        rating,
        user_ratings_id,
        id
 FROM public.user_ratings
 ORDER BY movie_id,
          rating,
          id
UNSEGMENTED ALL NODES;

select refresh('public.actor, public.acts_in, public.movie, public.user_ratings');

select make_ahm_now();

DROP PROJECTION public.actor_b0, public.actor_b1 CASCADE;

DROP PROJECTION public.acts_in_b0, public.acts_in_b1 CASCADE;

DROP PROJECTION public.movie_b0, public.movie_b1 CASCADE;

DROP PROJECTION public.user_ratings_b0, public.user_ratings_b1 CASCADE;

CREATE PROJECTION users_DBD_5_rep_movielens_design /*+createtype(D)*/
(
 users_id ENCODING DELTARANGE_COMP,
 gender ENCODING RLE,
 age_range ENCODING RLE,
 occupation ENCODING BLOCKDICT_COMP,
 zipcode ENCODING RLE,
 num_ratings ENCODING BLOCKDICT_COMP,
 id ENCODING DELTARANGE_COMP
)
AS
 SELECT users_id,
        gender,
        age_range,
        occupation,
        zipcode,
        num_ratings,
        id
 FROM public.users
 ORDER BY gender,
          age_range,
          zipcode,
          id
UNSEGMENTED ALL NODES;

select refresh('public.users');

select make_ahm_now();

DROP PROJECTION public.users_b0, public.users_b1 CASCADE;



    7 replies

    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • January 4, 2014
    Hi Matthew,

    Hm...  Thanks for the detailed design info.  Could you also post the query in question?  If you could post the output of "EXPLAIN <query>", that could also be interesting.  (Neither of these designs is fundamentally slower than the other; they'll be better at different types of queries.)

    Also, how long (generally speaking) was it taking before and after?  Is this 100ms to 400ms?  1 hour to 4 hours?

    Also, just to confirm, the query that you ran through the DBD is also the query that you're running and seeing the performance difference?  (If the two are different, try running the DBD again and including both queries.)

    Thanks,
    Adam
    Prasanta_Pal
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • January 4, 2014
    What was the query supplied to DBD and what other options are chosen while running DBD? Which projections are dropped?
    You can create the original projections and refresh them and then drop the projections created by DBD.

    matthewcornell
    Forum|alt.badge.img+1
    • matthewcornell
    • Author
    • Participating Frequently
    • January 4, 2014
    Re: before and after: 1s before, 4s after. Re: the query - Doh! The query follows, as does EXPLAIN output. And yes, it's the same query as DBD. Sorry I forgot these.

    -- query

    SELECT v1, v2, cond0
    FROM
      (SELECT designSub0.*, cond0
       FROM
         (SELECT outcome.baseId, v1, v2
          FROM
            (SELECT baseId, avg(is_female) AS v1
             FROM
               (SELECT DISTINCT acts_in.acts_in_id AS baseId,
                                actor.actor_id AS targetId,
                                actor.is_female AS is_female
                FROM acts_in,
                     actor
                WHERE acts_in.actor_id = actor.actor_id
                  AND actor.is_female IS NOT NULL) tempv1
             GROUP BY baseId) treatment,
            (SELECT baseId, avg(number_of_movies) AS v2
             FROM
               (SELECT DISTINCT acts_in.acts_in_id AS baseId,
                                acts_in.acts_in_id AS targetId,
                                acts_in.number_of_movies AS number_of_movies
                FROM acts_in
                WHERE acts_in.number_of_movies IS NOT NULL) tempv2
             GROUP BY baseId) outcome
          WHERE treatment.baseId = outcome.baseId) designSub0
       LEFT JOIN
         (SELECT baseId, avg(budget) AS cond0
          FROM
            (SELECT DISTINCT acts_in1.acts_in_id AS baseId,
                             movie2.movie_id AS targetId,
                             movie2.budget AS budget
             FROM acts_in acts_in1,
                  actor,
                  acts_in acts_in2,
                  movie movie1,
                  user_ratings user_ratings1,
                  users,
                  user_ratings user_ratings2,
                  movie movie2
             WHERE acts_in1.actor_id = actor.actor_id
               AND actor.actor_id = acts_in2.actor_id
               AND acts_in2.movie_id = movie1.movie_id
               AND movie1.movie_id = user_ratings1.movie_id
               AND user_ratings1.users_id = users.users_id
               AND users.users_id = user_ratings2.users_id
               AND user_ratings2.movie_id = movie2.movie_id
               AND movie1.movie_id <> movie2.movie_id
               AND acts_in1.acts_in_id <> acts_in2.acts_in_id
               AND user_ratings1.user_ratings_id <> user_ratings2.user_ratings_id
               AND movie2.budget IS NOT NULL) tempcond0
          GROUP BY baseId) condTable0 ON designSub0.baseId = condTable0.baseId) design;



    -- explain

     Access Path:
     +-JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 2K, Rows: 1 (NO STATISTICS)] (PATH ID: 2)
     |  Join Cond: (designSub0.baseId = condTable0.baseId)
     |  Execute on: All Nodes
     | +-- Outer -> SELECT [Cost: 133, Rows: 1 (NO STATISTICS)] (PATH ID: 3)
     | |      Execute on: All Nodes
     | | +---> JOIN MERGEJOIN(inputs presorted) [Cost: 133, Rows: 1 (NO STATISTICS)] (PATH ID: 4)
     | | |      Join Cond: (treatment.baseId = outcome.baseId)
     | | |      Execute on: All Nodes
     | | | +-- Outer -> SELECT [Cost: 62, Rows: 1 (NO STATISTICS)] (PATH ID: 5)
     | | | |      Execute on: All Nodes
     | | | | +---> GROUPBY PIPELINED (GLOBAL RESEGMENT GROUPS) [Cost: 62, Rows: 1 (NO STATISTICS)] (PATH ID: 6
    )
     | | | | |      Aggregates: sum_float(tempv2.number_of_movies), count(tempv2.number_of_movies)
     | | | | |      Group By: tempv2.baseId
     | | | | |      Execute on: All Nodes
     | | | | |      Runtime Filters: (SIP1(MergeJoin): outcome.baseId), (SIP1(MergeJoin): outcome.baseId), (SI
    P1(MergeJoin): outcome.baseId)
     | | | | |      Execute on: All Nodes
     | | | | | +---> GROUPBY HASH (SORT OUTPUT) (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 43,
    Rows: 4K (NO STATISTICS)] (PATH ID: 8)
     | | | | | |      Group By: acts_in.acts_in_id, acts_in.acts_in_id, acts_in.number_of_movies
     | | | | | |      Execute on: All Nodes
     | | | | | | +---> STORAGE ACCESS for acts_in [Cost: 10, Rows: 4K (NO STATISTICS)] (PATH ID: 9)
     | | | | | | |      Projection: public.acts_in_b0
     | | | | | | |      Materialize: acts_in.number_of_movies, acts_in.acts_in_id
     | | | | | | |      Filter: (acts_in.number_of_movies IS NOT NULL)
     | | | | | | |      Execute on: All Nodes
     | | | +-- Inner -> SELECT [Cost: 70, Rows: 1 (NO STATISTICS)] (PATH ID: 10)
     | | | |      Execute on: All Nodes
     | | | | +---> GROUPBY PIPELINED (GLOBAL RESEGMENT GROUPS) [Cost: 70, Rows: 1 (NO STATISTICS)] (PATH ID: 1
    1)
     | | | | |      Aggregates: sum_float(tempv1.is_female), count(tempv1.is_female)
     | | | | |      Group By: tempv1.baseId
     | | | | |      Execute on: All Nodes
     | | | | |      Execute on: All Nodes
     | | | | | +---> GROUPBY HASH (SORT OUTPUT) (LOCAL RESEGMENT GROUPS) [Cost: 51, Rows: 4K (NO STATISTICS)]
    (PATH ID: 13)
     | | | | | |      Group By: acts_in.acts_in_id, actor.actor_id, actor.is_female
     | | | | | |      Execute on: All Nodes
     | | | | | | +---> JOIN HASH [Cost: 47, Rows: 4K (NO STATISTICS)] (PATH ID: 14) Outer (RESEGMENT)(LOCAL RO
    UND ROBIN) Inner (RESEGMENT)
     | | | | | | |      Join Cond: (acts_in.actor_id = actor.actor_id)
     | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Outer -> STORAGE ACCESS for acts_in [Cost: 9, Rows: 4K (NO STATISTICS)] (PUSHED GROUPIN
    G) (PATH ID: 15)
     | | | | | | | |      Projection: public.acts_in_b0
     | | | | | | | |      Materialize: acts_in.actor_id, acts_in.acts_in_id
     | | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Inner -> STORAGE ACCESS for actor [Cost: 18, Rows: 2K (NO STATISTICS)] (PUSHED GROUPING
    ) (PATH ID: 16)
     | | | | | | | |      Projection: public.actor_b0
     | | | | | | | |      Materialize: actor.is_female, actor.actor_id
     | | | | | | | |      Filter: (actor.is_female IS NOT NULL)
     | | | | | | | |      Execute on: All Nodes
     | +-- Inner -> SELECT [Cost: 2K, Rows: 1 (NO STATISTICS)] (PATH ID: 17)
     | |      Execute on: All Nodes
     | | +---> GROUPBY PIPELINED (GLOBAL RESEGMENT GROUPS) [Cost: 2K, Rows: 1 (NO STATISTICS)] (PATH ID: 18)
     | | |      Aggregates: sum_float(tempcond0.budget), count(tempcond0.budget)
     | | |      Group By: tempcond0.baseId
     | | |      Execute on: All Nodes
     | | |      Execute on: All Nodes
     | | | +---> GROUPBY HASH (SORT OUTPUT) (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 2K, Rows
    : 59K (NO STATISTICS)] (PATH ID: 20)
     | | | |      Group By: acts_in1.acts_in_id, movie2.movie_id, movie2.budget
     | | | |      Execute on: All Nodes
     | | | | +---> JOIN HASH [Cost: 955, Rows: 59K (NO STATISTICS)] (PATH ID: 21) Outer (LOCAL ROUND ROBIN) In
    ner (BROADCAST)
     | | | | |      Join Cond: (movie1.movie_id = user_ratings1.movie_id)
     | | | | |      Join Filter: (movie1.movie_id <> movie2.movie_id)
     | | | | |      Execute on: All Nodes
     | | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 698, Rows: 59K (NO STATISTICS)] (PUSHED GR
    OUPING) (PATH ID: 22) Inner (RESEGMENT)
     | | | | | |      Join Cond: (user_ratings1.users_id = users.users_id)
     | | | | | |      Join Filter: (user_ratings1.user_ratings_id <> user_ratings2.user_ratings_id)
     | | | | | |      Execute on: All Nodes
     | | | | | |      Runtime Filter: (SIP2(HashJoin): user_ratings1.movie_id)
     | | | | | | +-- Outer -> JOIN HASH [Cost: 358, Rows: 59K (NO STATISTICS)] (PATH ID: 23) Outer (LOCAL ROUN
    D ROBIN) Inner (BROADCAST)
     | | | | | | |      Join Cond: (user_ratings2.movie_id = movie2.movie_id)
     | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 316, Rows: 59K (NO STATISTICS)] (PATH
    ID: 24) Outer (RESEGMENT) Inner (RESEGMENT)
     | | | | | | | |      Join Cond: (users.users_id = user_ratings2.users_id)
     | | | | | | | |      Execute on: All Nodes
     | | | | | | | |      Runtime Filters: (SIP4(HashJoin): user_ratings2.movie_id), (SIP3(MergeJoin): users.u
    sers_id)
     | | | | | | | | +-- Outer -> STORAGE ACCESS for user_ratings2 [Cost: 42, Rows: 59K (NO STATISTICS)] (PATH
     ID: 25)
     | | | | | | | | |      Projection: public.user_ratings_b0
     | | | | | | | | |      Materialize: user_ratings2.users_id, user_ratings2.movie_id, user_ratings2.user_ra
    tings_id
     | | | | | | | | |      Execute on: All Nodes
     | | | | | | | | +-- Inner -> STORAGE ACCESS for users [Cost: 5, Rows: 6K (NO STATISTICS)] (PATH ID: 26)
     | | | | | | | | |      Projection: public.users_b0
     | | | | | | | | |      Materialize: users.users_id
     | | | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Inner -> STORAGE ACCESS for movie2 [Cost: 9, Rows: 1K (NO STATISTICS)] (PATH ID: 27)
     | | | | | | | |      Projection: public.movie_b0
     | | | | | | | |      Materialize: movie2.budget, movie2.movie_id
     | | | | | | | |      Filter: (movie2.budget IS NOT NULL)
     | | | | | | | |      Execute on: All Nodes
     | | | | | | +-- Inner -> STORAGE ACCESS for user_ratings1 [Cost: 42, Rows: 59K (NO STATISTICS)] (PATH ID:
     28)
     | | | | | | |      Projection: public.user_ratings_b0
     | | | | | | |      Materialize: user_ratings1.users_id, user_ratings1.movie_id, user_ratings1.user_rating
    s_id
     | | | | | | |      Execute on: All Nodes
     | | | | | +-- Inner -> JOIN HASH [Cost: 95, Rows: 4K (NO STATISTICS)] (PUSHED GROUPING) (PATH ID: 29) Out
    er (RESEGMENT)(LOCAL ROUND ROBIN)
     | | | | | |      Join Cond: (actor.actor_id = acts_in2.actor_id)
     | | | | | |      Join Filter: (acts_in1.acts_in_id <> acts_in2.acts_in_id)
     | | | | | |      Materialize at Input: acts_in2.movie_id, acts_in2.actor_id, acts_in2.acts_in_id
     | | | | | |      Execute on: All Nodes
     | | | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 19, Rows: 4K (NO STATISTICS)] (PATH ID:
    30) Inner (BROADCAST)
     | | | | | | |      Join Cond: (acts_in2.movie_id = movie1.movie_id)
     | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Outer -> STORAGE ACCESS for acts_in2 [Cost: 5, Rows: 4K (NO STATISTICS)] (PATH ID: 31)
     | | | | | | | |      Projection: public.acts_in_b0
     | | | | | | | |      Materialize: acts_in2.movie_id
     | | | | | | | |      Execute on: All Nodes
     | | | | | | | |      Runtime Filter: (SIP5(MergeJoin): acts_in2.movie_id)
     | | | | | | | +-- Inner -> STORAGE ACCESS for movie1 [Cost: 5, Rows: 1K (NO STATISTICS)] (PATH ID: 32)
     | | | | | | | |      Projection: public.movie_b0
     | | | | | | | |      Materialize: movie1.movie_id
     | | | | | | | |      Execute on: All Nodes
     | | | | | | +-- Inner -> JOIN HASH [Cost: 35, Rows: 4K (NO STATISTICS)] (PATH ID: 33) Outer (RESEGMENT)(L
    OCAL ROUND ROBIN) Inner (RESEGMENT)
     | | | | | | |      Join Cond: (acts_in1.actor_id = actor.actor_id)
     | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Outer -> STORAGE ACCESS for acts_in1 [Cost: 9, Rows: 4K (NO STATISTICS)] (PATH ID: 34)
     | | | | | | | |      Projection: public.acts_in_b0
     | | | | | | | |      Materialize: acts_in1.actor_id, acts_in1.acts_in_id
     | | | | | | | |      Execute on: All Nodes
     | | | | | | | +-- Inner -> STORAGE ACCESS for actor [Cost: 9, Rows: 2K (NO STATISTICS)] (PATH ID: 35)
     | | | | | | | |      Projection: public.actor_b0
     | | | | | | | |      Materialize: actor.actor_id
     | | | | | | | |      Execute on: All Nodes
     
     EOF

    matthewcornell
    Forum|alt.badge.img+1
    • matthewcornell
    • Author
    • Participating Frequently
    • January 4, 2014
    The query is above, thank you. I used the same options as in the tutorial's "Step 5: Create a Comprehensive Design". Re: Which projections are dropped, I'm not sure what you mean. I was trying to ask where the superprojections are located - I could not find them listed.
    matthewcornell
    Forum|alt.badge.img+1
    • matthewcornell
    • Author
    • Participating Frequently
    • February 7, 2014
    Following up, I'd like to understand this as I have a 10 minute query on a small database that I'd like to tune.

    Question: Where are the superprojections located?

    Also, I have that 10m query running right now. How do I see performance information about it in the MC? I go to the Activity tab and see the (spiky) graph, and I can click on the line that moves left and right along with my mouse when I hover over the graph, but I can't find info about this current query. Is there a simple list of currently running queries.

    Thank you.
    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • April 25, 2014
    1- Make sure no NO STATISTICS appear in your explain plan
    2- Replace the DISTINCT with a GROUP BY
    3- Run a profile on your query and make sure the resource pool allocated to the user running the process can handle the load.

    matthewcornell
    Forum|alt.badge.img+1
    • matthewcornell
    • Author
    • Participating Frequently
    • May 1, 2014
    Thanks very much, Adrian.
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement