Hi I had to write big sql code returning max 10 rows (3 selects 7,10 and 13 joins, mostly equi-joins - too long to include). I tried do solve this in different ways: 1. create view Look at simple test.sql in attachement. Just make psql -f test.sql. I have PostgreSQL 7.1.2. I'm used to C,C++, not to SQL and I can't understand why selecting 1 row from K2 doesn't use index scan for only 1 row. -> Index Scan using k_pkey on k k1 (cost=0.00..2.02 rows=1 width=8) -> Index Scan using k_pkey on k k2 (cost=0.00..8.14 rows=10 width=8) Second view - bla2 works much worse... Does it correspond to my question about dynamic date which should be static (pgsql-bugs)? Views would be nice but only if we could send them params... 2. sql function I tried to return _int4 (array of int) - enough for me, but... how to make array of non-static int? '{0,1,2}' works fine, but '{id_t,id_k}' not 3. pl/pgsql function first step - create temp table (once per session) second step - pl/pgsql clears and inserts into that table. I think it's the best solution. It works really fast. I can split sql into smaller pieces and work on them separately. Does anyone know how to make function returning rows another way? Regards, Tomek
create table T( id_t integer not null PRIMARY KEY, nazwa varchar (80) ); create table K( id_k integer not null, id_t integer not null references T, data_k date not null, primary key(id_t,data_k) ); create table TP( id_t integer not null references T, nr smallint not null, date_diff smallint not null default 0 ); create index ind_tp_t on TP(id_t); insert into T values (1,'aa'); insert into T values (2,'bb'); insert into K values (1,1,'2001-09-12'); insert into K values (2,1,'2001-09-13'); insert into K values (3,1,'2001-09-14'); insert into K values (4,1,'2001-09-15'); insert into K values (5,2,'2001-09-12'); insert into K values (6,2,'2001-09-13'); insert into K values (7,2,'2001-09-14'); insert into K values (8,2,'2001-09-15'); insert into K values (9,2,'2001-09-16'); insert into TP values (1,1,0); insert into TP values (2,3,0); create view bla as select T1.id_t, K1.data_k+TP.date_diff as data1, TP.nr from (select * from T limit 1) T1 inner join TP on (TP.id_t=T1.id_t) inner join K K1 on (TP.id_t=K1.id_t and K1.data_k='2001-09-23') inner join K K2 on (TP.id_t=K2.id_t and K2.data_k=K1.data_k+TP.date_diff); create view bla2 as select T1.id_t, K1.data_k+TP.date_diff as data1, TP.nr from (select * from T limit 1) T1 inner join TP on (TP.id_t=T1.id_t) inner join K K1 on (TP.id_t=K1.id_t) -- and K1.data_k='2001-09-23') inner join K K2 on (TP.id_t=K2.id_t and K2.data_k=K1.data_k+TP.date_diff); select * from pg_indexes where tablename in ('t','k','tp'); explain select * from bla where id_t=1; explain select * from bla2 where id_t=1 and data1='2001-09-24'; drop view bla2; drop view bla; drop table TP; drop table K; drop table T;
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster