Re: [PERFORM] Caching of Queries
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > First, it's not a particular problem with pgpool. As far as I know any > connection pool solution has exactly the same problem. Second, it's > easy to fix if PostgreSQL provides a functionarity such as:"drop all > temporary tables if any". I don't like that definition exactly --- it would mean that every time we add more backend-local state, we expect client drivers to know to issue the right incantation to reset that kind of state. I'm thinking we need to invent a command like "RESET CONNECTION" that resets GUC variables, drops temp tables, forgets active NOTIFYs, and generally does whatever else needs to be done to make the session state appear virgin. When we add more such state, we can fix it inside the backend without bothering clients. I now realize that our "RESET ALL" command for GUC variables was not fully thought out. We could possibly redefine it as doing the above, but that might break some applications ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Caching of Queries
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > First, it's not a particular problem with pgpool. As far as I know any > > connection pool solution has exactly the same problem. Second, it's > > easy to fix if PostgreSQL provides a functionarity such as:"drop all > > temporary tables if any". > > I don't like that definition exactly --- it would mean that every time > we add more backend-local state, we expect client drivers to know to > issue the right incantation to reset that kind of state. > > I'm thinking we need to invent a command like "RESET CONNECTION" that > resets GUC variables, drops temp tables, forgets active NOTIFYs, and > generally does whatever else needs to be done to make the session state > appear virgin. When we add more such state, we can fix it inside the > backend without bothering clients. Great. It's much better than I propose. > I now realize that our "RESET ALL" command for GUC variables was not > fully thought out. We could possibly redefine it as doing the above, > but that might break some applications ... > > regards, tom lane > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] sequential scan on select distinct
I don't really think it would be a useful plan anyway. What *would* be useful is to support HashAggregate as an implementation alternative for DISTINCT --- currently I believe we only consider that for GROUP BY. The DISTINCT planning code is fairly old and crufty and hasn't been redesigned lately. regards, tom lane I see this as a minor annoyance only because I can write GROUP BY instead of DISTINCT and get the speed boost. It probably annoys people trying to port applications to postgres though, forcing them to rewrite their queries. * SELECT DISTINCT : 21442.296 ms (by default, uses an index scan) disabling index_scan => Sort + Unique : 14512.105 ms * GROUP BY : 1793.651 ms using HashAggregate * skip index scan by function : 13.833 ms The HashAggregate speed boost is good, but rather pathetic compared to a "skip index scan" ; but it's still worth having if updating the DISTINCT code is easy. Note that it would also benefit UNION queries which apparently use DISTINCT internally and currently produce this : -- explain analyze select number from ((select number from dummy) union (select number from dummy)) as foo; Subquery Scan foo (cost=287087.62..317087.62 rows=200 width=4) (actual time=33068.776..35575.330 rows=255 loops=1) -> Unique (cost=287087.62..297087.62 rows=200 width=4) (actual time=33068.763..35574.126 rows=255 loops=1) -> Sort (cost=287087.62..292087.62 rows=200 width=4) (actual time=33068.757..34639.180 rows=200 loops=1) Sort Key: number -> Append (cost=0.00..49804.00 rows=200 width=4) (actual time=0.055..7412.551 rows=200 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..24902.00 rows=100 width=4) (actual time=0.054..3104.165 rows=100 loops=1) -> Seq Scan on dummy (cost=0.00..14902.00 rows=100 width=4) (actual time=0.051..1792.348 rows=100 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..24902.00 rows=100 width=4) (actual time=0.048..3034.462 rows=100 loops=1) -> Seq Scan on dummy (cost=0.00..14902.00 rows=100 width=4) (actual time=0.044..1718.682 rows=100 loops=1) Total runtime: 36265.662 ms -- But could instead do this : explain analyze select number from ((select number from dummy) union all (select number from dummy)) as foo group by number; HashAggregate (cost=74804.00..74804.00 rows=200 width=4) (actual time=10753.648..10753.890 rows=255 loops=1) -> Subquery Scan foo (cost=0.00..69804.00 rows=200 width=4) (actual time=0.059..8992.084 rows=200 loops=1) -> Append (cost=0.00..49804.00 rows=200 width=4) (actual time=0.055..6688.639 rows=200 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..24902.00 rows=100 width=4) (actual time=0.054..2749.708 rows=100 loops=1) -> Seq Scan on dummy (cost=0.00..14902.00 rows=100 width=4) (actual time=0.052..1640.427 rows=100 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..24902.00 rows=100 width=4) (actual time=0.038..2751.916 rows=100 loops=1) -> Seq Scan on dummy (cost=0.00..14902.00 rows=100 width=4) (actual time=0.034..1637.818 rows=100 loops=1) Total runtime: 10754.120 ms -- A 3x speedup, but still a good thing to have. When I LIMIT the two subqueries to 100k rows instead of a million, the times are about equal. When I LIMIT one of the subqueries to 100k and leave the other to 1M, UNION ALL 17949.609 ms UNION + GROUP BY6130.417 ms Still some performance to be gained... -- Of course it can't use a skip index scan on a subquery, but I could instead : I know it's pretty stupid to use the same table twice but it's just an example. However, if you think about table partitions and views, a "select distinct number" from a view having multiple partitions would yield this type of query, and that table partitioning seems like a hot subject lately. let's create a dummy example view : create view dummy_view as (select * from dummy) union all (select * from dummy); explain analyze select number from dummy_view group by number; HashAggregate (cost=74804.00..74804.00 rows=200 width=4) (actual time=10206.456..10206.713 rows=255 loops=1) -> Subquery Scan dummy_view (cost=0.00..69804.00 rows=200 width=4) (actual time=0.060..8431.776 rows=200 loops=1) -> Append (cost=0.00..49804.00 rows=200 width=8) (actual time=0.055..6122.
Re: [PERFORM] Data warehousing requirements
Consider how the fact table is going to be used, and review hacking it up based on usage. Fact tables should be fairly narrow, so if there are extra columns beyond keys and dimension keys consider breaking it into parallel tables (vertical partitioning). Horizontal partitioning is your friend; especially if it is large - consider slicing the data into chunks. If the fact table is date driven it might be worthwhile to break it into separate tables based on date key. This wins in reducing the working set of queries and in buffering. If there is a real hotspot, such as current month's activity, you might want to keep a separate table with just the (most) active data.Static tables of unchanged data can simplify backups, etc., as well. Consider summary tables if you know what type of queries you'll hit. Especially here, MVCC is not your friend because it has extra work to do for aggregate functions. Cluster helps if you bulk load. In most warehouses, the data is downstream data from existing operational systems. Because of that you're not able to use database features to preserve integrity. In most cases, the data goes through an extract/transform/load process - and the output is considered acceptable. So, no RI is correct for star or snowflake design. Pretty much no anything else that adds intelligence - no triggers, no objects, no constraints of any sort. Many designers try hard to avoid nulls. On the hardware side - RAID5 might work here because of the low volume if you can pay the write performance penalty. To size hardware you need to estimate load in terms of transaction type (I usually make bucket categories of small, medium, and large effort needs) and transaction rate. Then try to estimate how much CPU and I/O they'll use. /Aaron "Let us not speak of them; but look, and pass on." - Original Message - From: "Gabriele Bartolini" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 06, 2004 5:36 PM Subject: [PERFORM] Data warehousing requirements > Hi guys, > > I just discussed about my problem on IRC. I am building a Web usage > mining system based on Linux, PostgreSQL and C++ made up of an OLTP > database which feeds several and multi-purpose data warehouses about users' > behaviour on HTTP servers. > > I modelled every warehouse using the star schema, with a fact table and > then 'n' dimension tables linked using a surrogate ID. > > Discussing with the guys of the chat, I came up with these conclusions, > regarding the warehouse's performance: > > 1) don't use referential integrity in the facts table > 2) use INTEGER and avoid SMALLINT and NUMERIC types for dimensions' IDs > 3) use an index for every dimension's ID in the fact table > > As far as administration is concerned: run VACUUM ANALYSE daily and > VACUUM FULL periodically. > > Is there anything else I should keep in mind? > > Also, I was looking for advice regarding hardware requirements for a > data warehouse system that needs to satisfy online queries. I have indeed > no idea at the moment. I can only predict 4 million about records a month > in the fact table, does it make sense or not? is it too much? > > Data needs to be easily backed up and eventually replicated. > > Having this in mind, what hardware architecture should I look for? How > many hard disks do I need, what kind and what RAID solution do you suggest > me to adopt (5 or 10 - I think)? > > Thank you so much, > -Gabriele > -- > Gabriele Bartolini: Web Programmer, ht://Dig & IWA/HWG Member, ht://Check > maintainer > Current Location: Prato, Toscana, Italia > [EMAIL PROTECTED] | http://www.prato.linux.it/~gbartolini | ICQ#129221447 > > "Leave every hope, ye who enter!", Dante Alighieri, Divine Comedy, The > Inferno > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.773 / Virus Database: 520 - Release Date: 05/10/2004 > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] sequential scan on select distinct
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <[EMAIL PROTECTED]> writes: > Present state is that DISTINCT and UNION are slow with or without using > the GROUP BY trick. Including the index skip scan in the planning options > would only happen when appropriate cases are detected. This detection > would be very fast. You have no basis whatever for making that last assertion; and since it's the critical point, I don't intend to let you slide by without backing it up. I think that looking for relevant indexes would be nontrivial; the more so in cases like you've been armwaving about just above, where you have to find a relevant index for each of several subqueries. The fact that the optimization wins a lot when it wins is agreed, but the time spent trying to apply it when it doesn't work is a cost that has to be set against that. I don't accept your premise that every query for which skip-index isn't relevant is so slow that planning time does not matter. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Excessive context switching on SMP Xeons
Alan Stange wrote: Here's a few numbers from the Opteron 250. If I get some time I'll post a more comprehensive comparison including some other systems. The system is a Sun v20z. Dual Opteron 250, 2.4Ghz, Linux 2.6, 8 GB memory. I did a compile and install of pg 8.0 beta 3. I created a data base on a tmpfs file system and ran pgbench. Everything was "out of the box", meaning I did not tweak any config files. I used this for pgbench: $ pgbench -i -s 32 and this for pgbench invocations: $ pgbench -s 32 -c 1 -t 1 -v clients tps 11290 2 1780 4176081680 16 1376 32904 The same test on a Dell PowerEdge 1750, Dual Xeon 3.2 GHz, 512k cache, HT on, Linux 2.4.21-20.ELsmp (RHEL 3), 4GB memory, pg 7.4.5: $ pgbench -i -s 32 pgbench $ pgbench -s 32 -c 1 -t 1 -v clients tps avg CS/sec --- - -- 1601 48,000 2889 77,000 4 1006 80,000 8985 59,000 16966 47,000 32913 46,000 Far less performance that the Dual Opterons with a low number of clients, but the gap narrows as the number of clients goes up. Anyone smarter than me care to explain? Anyone have a 4-way Opteron to run the same benchmark on? -Bill How are these results useful? In some sense, this is a speed of light number for the Opteron 250. You'll never go faster on this system with a real storage subsystem involved instead of a tmpfs file system. It's also a set of numbers that anyone else can reproduce as we don't have to deal with any differences in file systems, disk subsystems, networking, etc. Finally, it's a set of results that anyone else can compute on Xeon's or other systems and make a simple (and naive) comparisons. Just to stay on topic: vmstat reported about 30K cs / second while this was running the 1 and 2 client cases. -- Alan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] sequential scan on select distinct
Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> writes: > I see this as a minor annoyance only because I can write GROUP BY > instead of DISTINCT and get the speed boost. It probably annoys people > trying to port applications to postgres though, forcing them to rewrite > their queries. Yeah, really DISTINCT and DISTINCT ON are just special cases of GROUP BY. It seems it makes more sense to put the effort into GROUP BY and just have DISTINCT and DISTINCT ON go through the same code path. Effectively rewriting it internally as a GROUP BY. The really tricky part is that a DISTINCT ON needs to know about a first() aggregate. And to make optimal use of indexes, a last() aggregate as well. And ideally the planner/executor needs to know something is magic about first()/last() (and potentially min()/max() at some point) and that they don't need the complete set of tuples to calculate their results. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Data warehousing requirements
At 13.30 07/10/2004, Aaron Werman wrote: Consider how the fact table is going to be used, and review hacking it up based on usage. Fact tables should be fairly narrow, so if there are extra columns beyond keys and dimension keys consider breaking it into parallel tables (vertical partitioning). Hmm ... I have only an extra column. Sorry if I ask you to confirm this, but practically vertical partitioning allows me to divide a table into 2 tables (like if I cut them vertically, right?) having the same key. If I had 2 extra columns, that could be the case, couldn't it? Horizontal partitioning is your friend; especially if it is large - consider slicing the data into chunks. If the fact table is date driven it might be worthwhile to break it into separate tables based on date key. This wins in reducing the working set of queries and in buffering. If there is a real hotspot, such as current month's activity, you might want to keep a separate table with just the (most) active data.Static tables of unchanged data can simplify backups, etc., as well. In this case, you mean I can chunk data into: "facts_04_08" for the august 2004 facts. Is this the case? Otherwise, is it right my point of view that I can get good results by using a different approach, based on mixing vertical partitioning and the CLUSTER facility of PostgreSQL? Can I vertically partition also dimension keys from the fact table or not? However, this subject is awesome and interesting. Far out ... data warehousing seems to be really continous modeling, doesn't it! :-) Consider summary tables if you know what type of queries you'll hit. At this stage, I can't predict it yet. But of course I need some sort of summary. I will keep it in mind. Especially here, MVCC is not your friend because it has extra work to do for aggregate functions. Why does it have extra work? Do you mind being more precise, Aaron? It is really interesting. (thanks) Cluster helps if you bulk load. Is it maybe because I can update or build them once the load operation has finished? In most warehouses, the data is downstream data from existing operational systems. That's my case too. Because of that you're not able to use database features to preserve integrity. In most cases, the data goes through an extract/transform/load process - and the output is considered acceptable. So, no RI is correct for star or snowflake design. Pretty much no anything else that adds intelligence - no triggers, no objects, no constraints of any sort. Many designers try hard to avoid nulls. That's another interesting argument. Again, I had in mind the space efficiency principle and I decided to use null IDs for dimension tables if I don't have the information. I noticed though that in those cases I can't use any index and performances result very poor. I have a dimension table 'categories' referenced through the 'id_category' field in the facts table. I decided to set it to NULL in case I don't have any category to associate to it. I believe it is better to set a '0' value if I don't have any category, allowing me not to use a "SELECT * from facts where id_category IS NULL" which does not use the INDEX I had previously created on that field. On the hardware side - RAID5 might work here because of the low volume if you can pay the write performance penalty. To size hardware you need to estimate load in terms of transaction type (I usually make bucket categories of small, medium, and large effort needs) and transaction rate. Then try to estimate how much CPU and I/O they'll use. Thank you so much again Aaron. Your contribution has been really important to me. Ciao, -Gabriele "Let us not speak of them; but look, and pass on." P.S.: Dante rules ... :-) -- Gabriele Bartolini: Web Programmer, ht://Dig & IWA/HWG Member, ht://Check maintainer Current Location: Prato, Toscana, Italia [EMAIL PROTECTED] | http://www.prato.linux.it/~gbartolini | ICQ#129221447 > "Leave every hope, ye who enter!", Dante Alighieri, Divine Comedy, The Inferno --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.773 / Virus Database: 520 - Release Date: 05/10/2004 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] sequential scan on select distinct
Am Donnerstag, 7. Oktober 2004 14:01 schrieb Pierre-Frédéric Caillaud: > Side Note : > > What do you think about the idea of an "UniqueSort" which would do > sort+unique in one pass ? This is what oracle does and it is quite fast with it... -- Ole Langbehn freiheit.com technologies gmbh Theodorstr. 42-90 / 22761 Hamburg, Germany fon +49 (0)40 / 890584-0 fax +49 (0)40 / 890584-20 Freie Software durch Bücherkauf fördern | http://bookzilla.de/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] sequential scan on select distinct
Ole Langbehn <[EMAIL PROTECTED]> writes: >> What do you think about the idea of an "UniqueSort" which would do >> sort+unique in one pass ? > This is what oracle does and it is quite fast with it... Hashing is at least as fast, if not faster. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Excessive context switching on SMP Xeons
On Thu, Oct 07, 2004 at 11:48:41AM -0400, Bill Montgomery wrote: > Alan Stange wrote: > > The same test on a Dell PowerEdge 1750, Dual Xeon 3.2 GHz, 512k cache, > HT on, Linux 2.4.21-20.ELsmp (RHEL 3), 4GB memory, pg 7.4.5: > > Far less performance that the Dual Opterons with a low number of > clients, but the gap narrows as the number of clients goes up. Anyone > smarter than me care to explain? You'll have to wait for someone smarter than you, but I will posit this: Did you use a tmpfs filesystem like Alan? You didn't mention either way. Alan did that as an attempt remove IO as a variable. -Mike ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Excessive context switching on SMP Xeons
Bill Montgomery wrote: Alan Stange wrote: Here's a few numbers from the Opteron 250. If I get some time I'll post a more comprehensive comparison including some other systems. The system is a Sun v20z. Dual Opteron 250, 2.4Ghz, Linux 2.6, 8 GB memory. I did a compile and install of pg 8.0 beta 3. I created a data base on a tmpfs file system and ran pgbench. Everything was "out of the box", meaning I did not tweak any config files. I used this for pgbench: $ pgbench -i -s 32 and this for pgbench invocations: $ pgbench -s 32 -c 1 -t 1 -v clients tps 11290 2 1780 4176081680 16 1376 32904 The same test on a Dell PowerEdge 1750, Dual Xeon 3.2 GHz, 512k cache, HT on, Linux 2.4.21-20.ELsmp (RHEL 3), 4GB memory, pg 7.4.5: $ pgbench -i -s 32 pgbench $ pgbench -s 32 -c 1 -t 1 -v clients tps avg CS/sec --- - -- 1601 48,000 2889 77,000 4 1006 80,000 8985 59,000 16966 47,000 32913 46,000 Far less performance that the Dual Opterons with a low number of clients, but the gap narrows as the number of clients goes up. Anyone smarter than me care to explain? boy, did Thunderbird ever botch the format of the table I entered... I thought the falloff at 32 clients was a bit steep as well. One thought that crossed my mind is that "pgbench -s 32 -c 32 ..." might not be valid. From the pgbench README: -s scaling_factor this should be used with -i (initialize) option. number of tuples generated will be multiple of the scaling factor. For example, -s 100 will imply 10M (10,000,000) tuples in the accounts table. default is 1. NOTE: scaling factor should be at least as large as the largest number of clients you intend to test; else you'll mostly be measuring update contention. Another possible cause is the that pgbench process is cpu starved and isn't able to keep driving the postgresql processes. So I ran pgbench from another system with all else the same.The numbers were a bit smaller but otherwise similar. I then reran everything using -s 64: clients tps 1 1254 2 1645 4 1713 8 1548 161396 321060 Still starting to head down a bit. In the 32 client case, the system was ~60% user time, ~25% sytem and ~15% idle. Anyway, the machine is clearly hitting some contention somewhere. It could be in the tmpfs code, VM system, etc. -- Alan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Excessive context switching on SMP Xeons
Michael Adler wrote: On Thu, Oct 07, 2004 at 11:48:41AM -0400, Bill Montgomery wrote: Alan Stange wrote: The same test on a Dell PowerEdge 1750, Dual Xeon 3.2 GHz, 512k cache, HT on, Linux 2.4.21-20.ELsmp (RHEL 3), 4GB memory, pg 7.4.5: Far less performance that the Dual Opterons with a low number of clients, but the gap narrows as the number of clients goes up. Anyone smarter than me care to explain? You'll have to wait for someone smarter than you, but I will posit this: Did you use a tmpfs filesystem like Alan? You didn't mention either way. Alan did that as an attempt remove IO as a variable. -Mike Yes, I should have been more explicit. My goal was to replicate his experiment as closely as possible in my environment, so I did run my postgres data directory on a tmpfs. -Bill Montgomery ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Data warehousing requirements
Gabriele, > That's another interesting argument. Again, I had in mind the space > efficiency principle and I decided to use null IDs for dimension tables if > I don't have the information. I noticed though that in those cases I can't > use any index and performances result very poor. For one thing, this is false optimization; a NULL isn't saving you any table size on an INT or BIGINT column.NULLs are only smaller on variable-width columns. If you're going to start counting bytes, make sure it's an informed count. More importantly, you should never, ever allow null FKs on a star-topology database.LEFT OUTER JOINs are vastly less efficient than INNER JOINs in a query, and the difference between having 20 outer joins for your data view, vs 20 regular joins, can easily be a difference of 100x in execution time. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Data warehousing requirements
- Original Message -
From: "Gabriele Bartolini" <[EMAIL PROTECTED]>
To: "Aaron Werman" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, October 07, 2004 1:07 PM
Subject: Re: [PERFORM] Data warehousing requirements
> At 13.30 07/10/2004, Aaron Werman wrote:
> >Consider how the fact table is going to be used, and review hacking it up
> >based on usage. Fact tables should be fairly narrow, so if there are
extra
> >columns beyond keys and dimension keys consider breaking it into parallel
> >tables (vertical partitioning).
>
> Hmm ... I have only an extra column. Sorry if I ask you to confirm this,
> but practically vertical partitioning allows me to divide a table into 2
> tables (like if I cut them vertically, right?) having the same key. If I
> had 2 extra columns, that could be the case, couldn't it?
Yes - it's splitting a table's columns and copying the PK. If you have only
one column and it's narrow - partitioning becomes harder to justify.
>
> >Horizontal partitioning is your friend; especially if it is large -
consider
> >slicing the data into chunks. If the fact table is date driven it might
be
> >worthwhile to break it into separate tables based on date key. This wins
in
> >reducing the working set of queries and in buffering. If there is a real
> >hotspot, such as current month's activity, you might want to keep a
separate
> >table with just the (most) active data.Static tables of unchanged data
can
> >simplify backups, etc., as well.
>
> In this case, you mean I can chunk data into: "facts_04_08" for the august
> 2004 facts. Is this the case?
Exactly. The problem is when you need to query across the chunks. There was
a discussion here of creating views ala
create view facts as
select * from facts_04_07 where datekey between '01/07/2004' and
'31/07/2004'
union all
select * from facts_04_08 where datekey between '01/08/2004' and
'31/08/2004'
union all
select * from facts_04_09 where datekey between '01/09/2004' and
'30/09/2004'
...
hoping the restrictions would help the planner prune chunks out. Has anyone
tried this?
>
> Otherwise, is it right my point of view that I can get good results by
> using a different approach, based on mixing vertical partitioning and the
> CLUSTER facility of PostgreSQL? Can I vertically partition also dimension
> keys from the fact table or not?
If you can do that, you probably should beyond a star schema. The standard
definition of a star schema is a single very large fact table with very
small dimension tables. The point of a star is that it can be used to
efficiantly restrict results out by merging the dimensional restrictions and
only extracting matches from the fact table. E.g.,
select
count(*)
from
people_fact, /* 270M */
states_dim, /* only 50 something */
gender_dim, /* 2 */
age_dim /* say 115 */
where
age_dim.age > 65
and
gender_dim.gender = 'F'
and
states_dim.state_code in ('PR', 'ME')
and
age_dim.age_key = people_fact.age_key
and
gender_dim.gender_key = people_fact.gender_key
and
states_dim.state_key = people_fact.state_key
(I had to write out this trivial query because most DBAs don't realize going
in how ugly star queries are.) If you split the fact table so ages were in a
vertical partition you would optimize queries which didn't use the age data,
but if you needed the age data, you would have to join two large tables -
which is not a star query.
What you're thinking about on the cluster front is fun. You can split groups
of dimension keys off to seperate vertical partitions, but you can only
cluster each on a single key. So you need to split each one off, which
results in your inventing the index! (-:
>
> However, this subject is awesome and interesting. Far out ... data
> warehousing seems to be really continous modeling, doesn't it! :-)
>
> >Consider summary tables if you know what type of queries you'll hit.
>
> At this stage, I can't predict it yet. But of course I need some sort of
> summary. I will keep it in mind.
>
> >Especially here, MVCC is not your friend because it has extra work to do
for
> >aggregate functions.
>
> Why does it have extra work? Do you mind being more precise, Aaron? It is
> really interesting. (thanks)
The standard reasons - that a lot of queries that seem intuitively to be
resolvable statically or through indices have to walk the data to find
current versions. Keeping aggregates (especially if you can allow them to be
slightly stale) can reduce lots of reads. A big goal of horizontal
partitioning is to give the planner some way of reducing the query scope.
>
> >Cluster helps if you bulk load.
>
> Is it maybe because I can update or build them once the load operation has
> finished?
If you have streaming loads, clustering can be a pain to implement well.
>
> >In most warehouses, the data is downstream data from existing operational
> >systems.
>
> That's my case too.
>
> >Because of that you're not able to use database features to
>
Re: [PERFORM] Data warehousing requirements
Josh Berkus <[EMAIL PROTECTED]> writes: > For one thing, this is false optimization; a NULL isn't saving you any table > size on an INT or BIGINT column.NULLs are only smaller on variable-width > columns. Uh ... not true. The column will not be stored, either way. Now if you had a row that otherwise had no nulls, the first null in the column will cause a null-columns-bitmap to be added, which might more than eat up the savings from storing a single int or bigint. But after the first null, each additional null in a row is a win, free-and-clear, whether it's fixed-width or not. (There are also some alignment considerations that might cause the savings to vanish.) > More importantly, you should never, ever allow null FKs on a star-topology > database. LEFT OUTER JOINs are vastly less efficient than INNER JOINs in a > query, and the difference between having 20 outer joins for your data view, > vs 20 regular joins, can easily be a difference of 100x in execution time. It's not so much that they are necessarily inefficient as that they constrain the planner's freedom of action. You need to think a lot more carefully about the order of joining than when you use inner joins. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Data warehousing requirements
Tom, Well, I sit corrected. Obviously I misread that. > It's not so much that they are necessarily inefficient as that they > constrain the planner's freedom of action. You need to think a lot more > carefully about the order of joining than when you use inner joins. I've also found that OUTER JOINS constrain the types of joins that can/will be used as well as the order. Maybe you didn't intend it that way, but (for example) OUTER JOINs seem much more likely to use expensive merge joins. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
