Thank you very much for great sample.
I tried to create testcase from this to match production db:
1.2 million orders
3.5 million order details
13400 products with char(20) as primary keys containing ean-13 codes
mostly
3 last year data
every order has usually 1..3 detail lines
same product can appear multiple times in order
products are queried by start of code
This sample does not distribute products randomly between orders.
How to change this so that every order contains 3 (or 1..6 ) random
products?
I tried to use random row sample from
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks-i
but in this case constant product is returned always. It seems than
query containing randon() is executed only once.
You could try writing a plpgsql function which would generate the data
set.
Or you could use your existing data set.
By the way, a simple way to de-bloat your big table without blocking
would be this :
- stop all inserts and updates
- begin
- create table new like old table
- insert into new select * from old (order by perhaps)
- create indexes
- rename new into old
- commit
If this is just a reporting database where you insert a batch of new data
every day, for instance, that's very easy to do. If it's OLTP, then, no.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
