Comp 305-001/488-305: Database Administration

Spring 2017: Tuesdays, 4:15-6:45, Comm 010

Week 13

April 18


Homework 3: DellStore and query optimization




Homework 3 #4: how does Postgres know about the selectivity of the clause

    where products.price >= X

given that the histogram_bounds entry is null?

Here's the full response to this query (with \x in effect)

select * from pg_stats where tablename='products' and attname='price';

schemaname             | public
tablename              | products
attname                | price
inherited              | f
null_frac              | 0
avg_width              | 7
n_distinct             | 21
most_common_vals       | {18.99,27.99,11.99,17.99,22.99,14.99,29.99,21.99,20.99,28.99,12.99,16.99,13.99,23.99,15.99,25.99,9.99,24.99,26.99,10.99,19.99}
most_common_freqs      | {0.0513,0.0511,0.0504,0.0502,0.05,0.0493,0.0486,0.0483,0.0481,0.0477,0.0474,0.0471,0.0467,0.0465,0.0457,0.0457,0.0456,0.0456,0.0451,0.0449,0.0447}
histogram_bounds       |
correlation            | 0.0535264
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

So, while histogram_bounds is null, there's lots of comparable data in most_common_vals and most_common_freqs. The most_common_vals entry lists 21 prices, and, from this query

select count(distinct price) from products;

we see that there are only 21 distinct prices, so Postgres has listed them all.

select array_length(most_common_vals, 1) from pg_stats where tablename = 'products' and attname = 'price';


Transactions


Normalization


Permissions and Security

Sharding


NoSQL and CAP