Skip site navigation (1) Skip section navigation (2)

Re: Hash join on int takes 8..114 seconds

From: PFC <lists(at)peufeu(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Hash join on int takes 8..114 seconds
Date: 2008-11-22 23:24:52
Message-ID: op.uk1nbqcgcigqcu@soyouz (view raw or flat)
Thread:
Lists: pgsql-performance
> 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.

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-11-22 23:58:42
Subject: Re: seq scan over 3.3 million rows instead of single key index access
Previous:From: Gregory StarkDate: 2008-11-22 23:03:50
Subject: Re: seq scan over 3.3 million rows instead of single key index access

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group