Обсуждение: Indexes and Views

Hi PostgreSQL folks,
I would like to know is there any way to use indexes
with the views or rules in this situation:

cteate table test1(col1 int,col2 int,col3 int);

insert into test1 .....;
..............
insert into test1 .....;

create index test1_col1_col2 on test1(col1,col2);

create view test1_view as
       select col1/1000.0,
              col2/1000.0,
              col3/10.0
       from test1;

i'm storing col1 to col3 as integers to save storage
space(infact they are fixed point).I have many tables
ctrated using this template only the view devidor
constants are different for each table. Here they are
1000.0 1000.0 and 100.0 but for every table they are
different. I would like to be able to execute the
queryes like this using the index:

select * from test1_view
where col1=const1 and col2=const2;

select * from test2_view
where col1=const1 and col2=const2;

and etc.
not like this

select * from test1_view
where test1.col1=const1*1000
and test1.col2=const2*1000;

select * from test2_view
where test2.col1=const1*col1_dividor
and test2.col2=const2*col2_dividor;

I do not want the user application to know and to deal
with this table specific constant dividors.

So can I create some rules to be able to do this?
Is it possible to use index in this situation at all?
Is there any other way to get the same results?
I cheked the docs but could not find any solution.
Sorry for the long and badly formuled introduction !
Hopeing you will help me!

best regards,
Rumen


__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/

R D <mrk279@yahoo.com> writes:
> i'm storing col1 to col3 as integers to save storage
> space(infact they are fixed point).I have many tables
> ctrated using this template only the view devidor
> constants are different for each table. Here they are
> 1000.0 1000.0 and 100.0 but for every table they are
> different. I would like to be able to execute the
> queryes like this using the index:

This table design is sufficiently ugly and pointless that I feel no deep
remorse over the fact that you can't use an index that way ;-)

Why are you bothering with these divisors?  float4 is no larger than
int4 and will eliminate all of these pushups: just store the values
naturally.

If there really is some good reason to use scaled integers, you should
build yourself a scaled-integer datatype.

            regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> R D <mrk279@yahoo.com> writes:
> > i'm storing col1 to col3 as integers to save
> storage
> > space(infact they are fixed point).I have many
> tables
> > ctrated using this template only the view devidor
> > constants are different for each table. Here they
> are
> > 1000.0 1000.0 and 100.0 but for every table they
> are
> > different. I would like to be able to execute the
> > queryes like this using the index:
>
> This table design is sufficiently ugly and pointless
> that I feel no deep
> remorse over the fact that you can't use an index
> that way ;-)
> Why are you bothering with these divisors?  float4
> is no larger than
> int4 and will eliminate all of these pushups: just
> store the values
> naturally.

I know that this table design is ugly, but I have no
choise. Infact I need 9 decimal places which will fit
in float8 (two colums of the table) and 4 decimal
places (1 colum) fits in float4. Now I'm using int4
and int2. for this colums. So this way I'm saving 10
bytes per row. This tables are star catalogs and 3 of
them has over a 2-3 milion entries and one of them has
25,000,000 entryes (GSC-ACT catalog), regarding the
fact that this three colums are Indexed you can
imagine how much space is lost. And there is one more
thing I'm palnning to use the largest star catalog
(USNO-A2) with over 500 milions of stars.
Anyway if you have any better idea please share it
with me!

regards,
Rumen

__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/