Thread: 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/