Explain and Dellstore

Due Apr 22

There are five queries here for the DELLSTORE database, each involving a join involving some or all of the tables
Information on the DELLSTORE database is below.

You can enable a more vertical output format with the psql command
    \x on
if you wish.

For each of the queries below, 1-6, you should do the following regarding the Postgres 9.5 query plan:

1. An exact match on customers.lastname.

explain
select p.title
from customers c join orders o on c.customerid = o.customerid
    join orderlines ol on o.orderid = ol.orderid
    join products p on ol.prod_id = p.prod_id
where c.lastname = 'YHOODAJPYJ';


2. Same but with the join listed in the reverse order:

explain
select p.title
from products p join orderlines ol on ol.prod_id = p.prod_id
   join orders o on o.orderid = ol.orderid
   join customers c on c.customerid = o.customerid
where c.lastname = 'YHOODAJPYJ';

Question 2: Does the actual join order change when the join is listed in the reverse order? Or is the actual join order for #2 the same as for #1?


3. Range match on orderlines.prod_id (there is NOT an index on this attribute)

explain
select c.lastname
from customers c join orders o on c.customerid = o.customerid
    join orderlines ol on o.orderid = ol.orderid
where ol.prod_id <= 5;

Question 3: Does adding an index on orderlines.prod_id help?

Create the index with
    create index orderprodex on orderlines(prod_id);

Delete the index later with
    drop index orderprodex;


4. Range match on products.price

explain
select c.lastname
from customers c join orders o on c.customerid = o.customerid
    join orderlines ol on o.orderid = ol.orderid
    join products p on ol.prod_id = p.prod_id
where p.price < 10.0;

Question 4: As you increase the price threshold from 10.0, at some point the query plan changes. At what price threshold (to at least one decimal place) does the query plan first change? 11.0? 12.0? 12.5?


5. Range map on customer.zip (like #4 except on a different table)

explain
select p.title
from products p join orderlines ol on p.prod_id = ol.prod_id
    join orders o on o.orderid = ol.orderid
    join customers c on c.customerid = o.customerid
where c.zip between 60600 and 60699;

Question 5: does an index on customers.zip change anything? Create and drop the index with:
    create index zipdex on customers(zip);
    drop index zipdex;


6. Exact match on products.title

explain
select c.lastname
from customers c join orders o on c.customerid = o.customerid
    join orderlines ol on o.orderid = ol.orderid
    join products p on ol.prod_id = p.prod_id
where p.title = 'ACE PIRATES';

Question 6: does an index on products.title change anything? Create and drop the index with:
    create index titledex on products(title);
    drop index titledex;


Dellstore

The Dellstore data is in one large file, dellstore.sql. This file includes create table commands and data-insertion commands.

Here's how to install the tables. You can either create a new named database or else just load the tables into your existing company/university database.

To create a new named database for Dellstore:

    createdb -U postgres dellstore
or
    createdb -U postgres -W dellstore    # -W to force request for password

To load the information into Dellstore:

From the shell:
    psql dellstore < dellstore.sql
Alternatively, from within postgres, where psql was started in the directory containing dellstore.sql:
    \i dellstore.sql

If you are not creating a new database, just use
    psql < dellstore.sql


To get those nice diagrams of the query plans that I displayed in class:

1. put the query into a small file, eg 1.sql, and change the explain line to

explain (format json)

2. Run the query from the command line using

psql -qAt -f 1.sql dellstore > 1.json

3. Open the JSON output file in an editor, and paste its contents into the screen at tatiyants.com/pev/#/plans/new. Then click submit.