On 3/9/15 8:17 AM, Nicolas Paris wrote:
(sorry for top-posting, gmail does not help.)
*shakes fist at gmail*
Thanks to your advice Jim, I have done an other test :
No partial indexes, just a partial index on boolean columns does the
job. (I get same perfs as MV)
CREATE INDEX ..ON
Why are you dropping and re-loading? You mentioned it before and it
sounded like it had something to do with adding columns, but you
don't have to drop and reload to add a column.
Adding a NULL column is fast. Dropping one too. I need to set some row as
TRUE. I can do it with an update,
(sorry for top-posting, gmail does not help.)
Thanks to your advice Jim, I have done an other test :
No partial indexes, just a partial index on boolean columns does the job.
(I get same perfs as MV)
CREATE INDEX ..ON (BoolColumnX) WHERE BoolColumnX IS TRUE
Then VIEW =
SELECT colAcolZ
FROM
Thanks Jim,
My suggestion is to test using nothing but plain views and plain indexes on
the base table
Actualy the way I choose subset rows for views is complicated in terms of
query. Then using simple views without partial indexes is terrible in terms
of performance (I have tested that).
You
On 3/6/15 2:16 AM, Nicolas Paris wrote:
If you have that many different views I doubt you want that many
indexes anyway.
It's a datawarehouse, then each view is used by many user for each query.
Those views must be subset of the huge material table. All indexes are
needed
Yes, but
According to this link
http://postgresql.nabble.com/NULL-saves-disk-space-td4344106.html
NULL values do not take place if only one other column are null for that
row.
Boolean takes 1 byte wheras smallint 2bytes.
Then the space problem is not anymore a problem with boolean columns 95%
empty
One
If you have that many different views I doubt you want that many indexes
anyway.
It's a datawarehouse, then each view is used by many user for each query.
Those views must be subset of the huge material table. All indexes are
needed
Have you tried just hitting the base table and indexes
On 2/20/15 12:09 PM, Nicolas Paris wrote:
Well it seems that max query size for CREATE INDEX is 8160 character in
my 9.3 postgresql version.
Then the only solution see is to add a new boolean field : huge_table.view1
and change predicat to WHERE view1=1
But I may have 800 views.. adding 800
Hello !
I have a huge table, 1 bilion rows, with many indexes.
I have many materialysed view (MV), subsets of this huge table, with same
kind indexes.
I have many users, querying thoses MV.
I have a storage problem, because of MV physical memory use.
I wonder :
If I replace MV with classical
Thanks,
I like the idea of partial indexes mixed with simple Views
So question :
huge_table{
id,
field
}
CREATE INDEX idx_huge_table ON huge_table(id)
CREATE INDEX idx_huge_table_for_view1 ON huge_table(id) WHERE id IN (1,2,3)
CREATE VIEW view1 AS SELECT * FROM huge_table WHERE id IN (1,2,3)
On Fri, Feb 20, 2015 at 8:28 AM, Nicolas Paris nipari...@gmail.com wrote:
If I replace MV with classical Views, the only indexes that will be used
will be the huge table's one. As all users will query on the same indexes,
is will always be loaded in memory, right ? This will be shared, I mean
On Fri, Feb 20, 2015 at 11:06 AM, Nicolas Paris nipari...@gmail.com wrote:
Thanks,
I like the idea of partial indexes mixed with simple Views
So question :
huge_table{
id,
field
}
CREATE INDEX idx_huge_table ON huge_table(id)
CREATE INDEX idx_huge_table_for_view1 ON huge_table(id)
It appears that in the predicate close (WHERE id IN (foo)), foo cannot
depend on other table (join or other). It must be a list. I anderstand why
(this must be static).
I can build a string value, but in some case, I will have a milion key list.
Postgresql do not have limitation in query size, and
Well it seems that max query size for CREATE INDEX is 8160 character in my
9.3 postgresql version.
Then the only solution see is to add a new boolean field : huge_table.view1
and change predicat to WHERE view1=1
But I may have 800 views.. adding 800 new fields indexed to the huge table
is
14 matches
Mail list logo
