From: PFC <lists@peufeu.com>
To: Andrus <kobruleht2@hot.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Sun, 23 Nov 2008 00:24:52 +0100
Message-ID: <op.uk1nbqcgcigqcu@soyouz> (raw)
In-Reply-To: <E3AFB54F617D47F4BE652E85ECC67153@andrusnotebook>
References: <gg1sv3$27fr$1@news.hub.org>
<49253FE1.3070706@archonet.com>
<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
<op.ukw669uvcigqcu@soyouz>
<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
<op.ukze24t0cigqcu@soyouz>
<E3AFB54F617D47F4BE652E85ECC67153@andrusnotebook>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, kobruleht2@hot.ee
Subject: Re: Hash join on int takes 8..114 seconds
In-Reply-To: <op.uk1nbqcgcigqcu@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox