1.
<2>
Lot :
select relname, reltuples, relpages from pg_class where relname='lot';
relname | reltuples | relpages
---------+-----------+----------
customer | 249999 | 2865
Customer :
select relname, reltuples, relpages from pg_class where relname='customer';
relname | reltuples | relpages
---------+-----------+----------
customer | 249999 | 1908
Customer_lot :
select relname, reltuples, relpages from pg_class where relname='customer_lot';
relname | reltuples | relpages
---------+-----------+----------
customer_lot | 249999 | 1352
blocking factor for lot is: 249999/2865 = 87.
blocking factor for customer is:249999/1908 = 131.
blocking factor for customer_lot is: 249999/1352 = 184.
<3>
(a)
the query:
select lot_id from lot where lot_size between 300 and 15000;
This query's plan:
explain analyze select lot_id from lot where lot_size between 300 and 5000;
QUERY PLAN:
Seq Scan on lot (cost=0.00..6614.98 rows=1250 width=4) (actual time=7.68..1085.56 rows=147220 loops=1)
Total runtime: 1192.60 msec
(b)
the query:
select lot_id from lot where lot_value between 3000 and 15000;
This query's plan:
explain analyze select lot_id from lot where lot_value between 3000 and 15000;
QUERY PLAN:
Seq Scan on lot (cost=0.00..6614.98 rows=1250 width=4) (actual time=0.20..865.91 rows=3045 loops=1)
Total runtime: 868.54 msec
(c)
explain analyze select * from customer where customer_id=12;
QUERY PLAN:
Seq Scan on customer (cost=0.00..5032.99 rows=1 width=22) (actual time=16.04..4580.18 rows=1 loops=1)
Total runtime: 4580.31 msec
(d)
explain analyze insert into customer values (250001, 'Vince', 'Smith' );
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
Total runtime: 0.38 msec
(e)
explain analyze delete from customer where customer_id='250001';
QUERY PLAN:
