Skip to main content

Updating empty table that has projections

  • October 23, 2017
  • 2 replies
  • 11 views
chaima
Forum|alt.badge.img+2
  • chaima
  • Participating Frequently

Hello,

Updating an empty table that has projections takes a relatively long time even though no action will be performed eventually. Can any one help understand this behaviour? why can't the optimizer tell that the table is empty when establishing the plan? How can one optimize this? Note that we can't tell in advance whether these tables will be empty or not.

Here is a simple example:

CREATE TABLE new_table1 LIKE table1 INCLUDING PROJECTIONS;

UPDATE new_table1 r
SET email=n.email
FROM table2 n
WHERE r.name = n.name;

 -- OUTPUT
------
      -- 0
-- (1 row)

-- Time: First fetch (1 row): 2722.300 ms. All rows formatted: 2722.337 ms

On an empty table that has no projection, the execution will be almost instant

CREATE TABLE new_table2 LIKE table1;

UPDATE new_table2 r
SET email=n.email
FROM table2 n
WHERE r.name = n.name;

 -- OUTPUT
------
      -- 0
-- (1 row)

-- Time: First fetch (1 row): 50.612 ms. All rows formatted: 50.641 ms

Many thanks,

Chaima BERRACHDI

    2 replies

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • October 23, 2017

    Hi,

    How many projections are there? When I try this scenario in Vertica 9, I see a slight performance hit when there are 3 projections vs 0 projections...

    Example:

    dbadmin=> select count(*) from big_table;
  count
----------
 17259456
(1 row)

dbadmin=> select count(*) from projections where anchor_table_name = 'big_table';
 count
-------
     3
(1 row)

dbadmin=> create table big_table1 like big_table including projections;
CREATE TABLE

dbadmin=> create table big_table2 like big_table;
CREATE TABLE

dbadmin=> \timing
Timing is on.

dbadmin=> update big_table1 set owner_name = 'jim' where owner_name = 'dbadmin';
 OUTPUT
--------
      0
(1 row)

Time: First fetch (1 row): 57.964 ms. All rows formatted: 58.014 ms

dbadmin=> update big_table2 set owner_name = 'jim' where owner_name = 'dbadmin';
 OUTPUT
--------
      0
(1 row)

Time: First fetch (1 row): 9.180 ms. All rows formatted: 9.249 ms
    chaima
    Forum|alt.badge.img+2
    • chaima
    • Author
    • Participating Frequently
    • October 24, 2017

    Hi Jim,

    I'm sorry, someone was running other tests on the cluster so the excution times were higher than they should but there is still a big difference in execution time between empty tables with/without projections(there are two projections), and it takes longer when the table expressions get more complicated (see example below)

    Updated execution time:
    dbadmin=> select count(*) from table1;
    count
    ---------
    1000000
    (1 row)

    dbadmin=> select count(*) from projections where anchor_table_name='table1';
 count
-------
     2
(1 row)


dbadmin=> UPDATE new_table1 r
dbadmin-> SET email=n.email
dbadmin-> FROM table2 n
dbadmin-> WHERE r.name = n.name
dbadmin-> ;
 OUTPUT
--------
      0
(1 row)

Time: First fetch (1 row): 291.069 ms. All rows formatted: 291.111 ms
dbadmin=> UPDATE new_table1 r
dbadmin-> SET email=n.email
dbadmin-> FROM table2 n
dbadmin-> WHERE r.name = n.name and r.email=n.email;
 OUTPUT
--------
      0
(1 row)

Time: First fetch (1 row): 2442.388 ms. All rows formatted: 2442.420 ms


dbadmin=> UPDATE new_table2 r
dbadmin-> SET email=n.email
dbadmin-> FROM table2 n
dbadmin-> WHERE r.name = n.name;
 OUTPUT
--------
      0
(1 row)

Time: First fetch (1 row): 7.756 ms. All rows formatted: 7.793 ms
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement