Hi there.

I have tried to implement the layered views as suggested earlier on one of the simplest queries (just to get a feel for it). And there seems to be something odd going on.

Attached are all the statemens needed to see, how the database is made and the contents of postgresql.conf and two explain analyzes:

The machine is a single cpu Xeon, with 2G of memory and 2 scsi-drives in a mirror (is going to be extended to 6 within a few weeks) running 8.1beta3. The whole database has been vacuum analyzed just before the explain analyzes.

I have spend a few hours fiddling around with the performance of it, but seems to go nowhere - I might have become snowblind and missed something obvious though.

There are a few things, that strikes me:
- the base view (ord_result_pct) is reasonable fast (41 ms) - it does a lot of seq scans, but right now there are not enough data there to do otherwise - the pretty version (for output) is 17,5 times slower (722ms) even though it just joins against three tiny tables ( < 100 rows each) and the plan seems very different - the slow query (the _pretty) has lower expected costs as the other ( 338 vs 487 "performance units") , this looks like some cost parameters need tweaking. I cannot figure out which though. - the top nested loop seems to eat most of the time, I have a little trouble seeing what this nested loop is doing there anyways

Thanks in advance

Svenne
create table nb_property_type(  
        id integer not null,
        description_dk varchar not null,
        description_us varchar not null,
        primary key(id)
);

--- 8 rows in nb_property_type, not growing


create table groups (
id int4 not null default nextval('role_id_seq'),
groupname varchar not null,
is_home_group bool not null default 'f'::bool,
valid bool not null default 't'::bool,
created_at timestamp not null default current_timestamp,
changed_at timestamp,
stopped_at timestamp,
primary key(id));

-- at the moment approx. 20 rows, expected a few hundres when going online

create table ord_dataset(
        id serial,
        first_observation date not null,
        last_observation date,
        is_mainline bool not null default 't',
        is_visible bool not null default 'f',
        description_dk varchar,
        description_us varchar,
        created_by int4 not null references users,
        created_at timestamp not null default current_timestamp,
        primary key(id)
);

create unique index ord_dataset_fo_idx on ord_dataset(first_observation) where 
is_mainline = 't';

-- approx. 35 rows, growing 4 rows each year


create table ord_entrydata_current(
        dataset_id integer not null references ord_dataset,
        institut integer not null references groups,
        nb_property_type_id int4 not null references nb_property_type,
        amount int8 not null
);

create index ord_ed_cur_dataset_id on ord_entrydata_current(dataset_id);
create index ord_ed_cur_institut on ord_entrydata_current(institut);
create index ord_ed_cur_propertytype on 
ord_entrydata_current(nb_property_type_id);


-- filled by a trigger, approx. 3,000 rows, grows approx. 250 rows each year

create view ord_property_type_sums as
 SELECT ord_entrydata_current.dataset_id, 0 AS nb_property_type_id, 
ord_entrydata_current.institut, sum(ord_entrydata_current.amount) AS amount
   FROM ord_entrydata_current
  GROUP BY ord_entrydata_current.dataset_id, ord_entrydata_current.institut;

create view ord_property_type_all as
 SELECT ord_property_type_sums.dataset_id, 
ord_property_type_sums.nb_property_type_id, ord_property_type_sums.institut, 
ord_property_type_sums.amount
   FROM ord_property_type_sums
UNION ALL
 SELECT ord_entrydata_current.dataset_id, 
ord_entrydata_current.nb_property_type_id, ord_entrydata_current.institut, 
ord_entrydata_current.amount
   FROM ord_entrydata_current;

create view ord_institutes_sum as
 SELECT ord_property_type_all.dataset_id, 
ord_property_type_all.nb_property_type_id, 0 AS institut, 
sum(ord_property_type_all.amount) AS amount
   FROM ord_property_type_all
 GROUP BY ord_property_type_all.dataset_id, 
ord_property_type_all.nb_property_type_id;

create view ord_result_pct as
 SELECT t1.dataset_id, t1.nb_property_type_id, t1.institut, t1.amount / 
t2.amount * 100::numeric AS pct
   FROM ord_property_type_all t1, ord_institutes_sum t2
  WHERE t1.dataset_id = t2.dataset_id AND t1.nb_property_type_id = 
t2.nb_property_type_id;

create view ord_result_pct_pretty as
        select od.id, od.first_observation, od.description_dk as dsd_dk, 
od.description_us as dsd_us ,g.groupname,orp.institut, orp.nb_property_type_id, 
npt.description_dk as pd_dk, npt.description_us as pd_us, pct   from 
ord_result_pct orp, ord_dataset od, nb_property_type npt, groups g
                where orp.dataset_id = od.id and orp.institut = g.id and 
orp.nb_property_type_id = npt.id and od.is_visible = 't'::bool;


-- contents of postgresql.conf 


listen_addresses = 'localhost'
port = 5432
max_connections = 100 
superuser_reserved_connections = 1
shared_buffers = 20000
work_mem = 10240 
maintenance_work_mem = 163840
max_stack_depth = 2048
max_fsm_pages = 50000
max_fsm_relations = 3000 
max_files_per_process = 1000
bgwriter_delay = 200
bgwriter_all_percent = 1.0 
bgwriter_all_maxpages = 10 
fsync = on 
wal_buffers = 128 
checkpoint_segments = 32 
effective_cache_size = 50000


-- now for the queries
rkr=# explain analyze select * from ord_result_pct ;
                                                                        QUERY 
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=466.54..487.20 rows=15 width=76) (actual time=30.185..39.857 
rows=2532 loops=1)
   Merge Cond: (("outer".nb_property_type_id = "inner".nb_property_type_id) AND 
("outer".dataset_id = "inner".dataset_id))
   ->  Sort  (cost=286.05..292.24 rows=2476 width=44) (actual 
time=14.591..15.519 rows=2532 loops=1)
         Sort Key: t1.nb_property_type_id, t1.dataset_id
         ->  Append  (cost=54.38..121.72 rows=2476 width=44) (actual 
time=4.895..10.879 rows=2532 loops=1)
               ->  HashAggregate  (cost=54.38..57.20 rows=226 width=16) (actual 
time=4.894..5.111 rows=282 loops=1)
                     ->  Seq Scan on ord_entrydata_current  (cost=0.00..37.50 
rows=2250 width=16) (actual time=0.004..1.271 rows=2250 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..60.00 rows=2250 
width=20) (actual time=0.005..4.162 rows=2250 loops=1)
                     ->  Seq Scan on ord_entrydata_current  (cost=0.00..37.50 
rows=2250 width=20) (actual time=0.002..1.669 rows=2250 loops=1)
   ->  Sort  (cost=180.49..181.11 rows=248 width=40) (actual 
time=15.578..16.533 rows=2526 loops=1)
         Sort Key: t2.nb_property_type_id, t2.dataset_id
         ->  Subquery Scan t2  (cost=165.05..170.63 rows=248 width=40) (actual 
time=14.597..15.014 rows=288 loops=1)
               ->  HashAggregate  (cost=165.05..168.15 rows=248 width=40) 
(actual time=14.595..14.822 rows=288 loops=1)
                     ->  Append  (cost=54.38..121.72 rows=2476 width=44) 
(actual time=4.901..11.027 rows=2532 loops=1)
                           ->  HashAggregate  (cost=54.38..57.20 rows=226 
width=16) (actual time=4.901..5.105 rows=282 loops=1)
                                 ->  Seq Scan on ord_entrydata_current  
(cost=0.00..37.50 rows=2250 width=16) (actual time=0.002..1.308 rows=2250 
loops=1)
                           ->  Subquery Scan "*SELECT* 2"  (cost=0.00..60.00 
rows=2250 width=20) (actual time=0.006..4.312 rows=2250 loops=1)
                                 ->  Seq Scan on ord_entrydata_current  
(cost=0.00..37.50 rows=2250 width=20) (actual time=0.002..1.697 rows=2250 
loops=1)
 Total runtime: 41.076 ms
(19 rows)

rkr=# explain analyze select * from ord_result_pct_pretty ;
                                                                        QUERY 
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=223.09..338.61 rows=1 width=174) (actual 
time=20.213..721.361 rows=2250 loops=1)
   Join Filter: (("outer".dataset_id = "inner".dataset_id) AND 
("outer".nb_property_type_id = "inner".nb_property_type_id))
   ->  Hash Join  (cost=58.04..164.26 rows=1 width=150) (actual 
time=5.510..22.088 rows=2250 loops=1)
         Hash Cond: ("outer".institut = "inner".id)
         ->  Hash Join  (cost=56.88..163.00 rows=16 width=137) (actual 
time=5.473..19.165 rows=2250 loops=1)
               Hash Cond: ("outer".dataset_id = "inner".id)
               ->  Hash Join  (cost=55.48..160.95 rows=99 width=101) (actual 
time=5.412..16.264 rows=2250 loops=1)
                     Hash Cond: ("outer".nb_property_type_id = "inner".id)
                     ->  Append  (cost=54.38..121.72 rows=2476 width=44) 
(actual time=4.900..12.869 rows=2532 loops=1)
                           ->  HashAggregate  (cost=54.38..57.20 rows=226 
width=16) (actual time=4.900..5.094 rows=282 loops=1)
                                 ->  Seq Scan on ord_entrydata_current  
(cost=0.00..37.50 rows=2250 width=16) (actual time=0.002..1.266 rows=2250 
loops=1)
                           ->  Subquery Scan "*SELECT* 2"  (cost=0.00..60.00 
rows=2250 width=20) (actual time=0.009..6.063 rows=2250 loops=1)
                                 ->  Seq Scan on ord_entrydata_current  
(cost=0.00..37.50 rows=2250 width=20) (actual time=0.002..2.755 rows=2250 
loops=1)
                     ->  Hash  (cost=1.08..1.08 rows=8 width=57) (actual 
time=0.016..0.016 rows=8 loops=1)
                           ->  Seq Scan on nb_property_type npt  
(cost=0.00..1.08 rows=8 width=57) (actual time=0.002..0.010 rows=8 loops=1)
               ->  Hash  (cost=1.32..1.32 rows=32 width=36) (actual 
time=0.054..0.054 rows=32 loops=1)
                     ->  Seq Scan on ord_dataset od  (cost=0.00..1.32 rows=32 
width=36) (actual time=0.003..0.027 rows=32 loops=1)
                           Filter: is_visible
         ->  Hash  (cost=1.13..1.13 rows=13 width=17) (actual time=0.029..0.029 
rows=13 loops=1)
               ->  Seq Scan on groups g  (cost=0.00..1.13 rows=13 width=17) 
(actual time=0.007..0.019 rows=13 loops=1)
   ->  HashAggregate  (cost=165.05..168.15 rows=248 width=40) (actual 
time=0.007..0.204 rows=288 loops=2250)
         ->  Append  (cost=54.38..121.72 rows=2476 width=44) (actual 
time=4.983..11.132 rows=2532 loops=1)
               ->  HashAggregate  (cost=54.38..57.20 rows=226 width=16) (actual 
time=4.982..5.192 rows=282 loops=1)
                     ->  Seq Scan on ord_entrydata_current  (cost=0.00..37.50 
rows=2250 width=16) (actual time=0.001..1.333 rows=2250 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..60.00 rows=2250 
width=20) (actual time=0.008..4.329 rows=2250 loops=1)
                     ->  Seq Scan on ord_entrydata_current  (cost=0.00..37.50 
rows=2250 width=20) (actual time=0.002..1.747 rows=2250 loops=1)
 Total runtime: 722.350 ms
(27 rows)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Reply via email to