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

Re: performance tuning queries

From: PFC <lists(at)peufeu(dot)com>
To: "Kevin Kempter" <kevink(at)consistentstate(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: performance tuning queries
Date: 2008-11-27 08:38:36
Message-ID: op.uk9rmmqncigqcu@soyouz (view raw or flat)
Thread:
Lists: pgsql-performance

> First off, any thoughts per tuning inserts into large tables. I have a  
> large
> table with an insert like this:
>
> insert into public.bigtab1 (text_col1, text_col2, id) values ...
>
>                 QUERY PLAN
> ------------------------------------------
>  Result  (cost=0.00..0.01 rows=1 width=0)
> (1 row)
>
> The query cost is low but this is one of the slowest statements per  
> pgfouine

	Possible Causes of slow inserts :

	- slow triggers ?
	- slow foreign key checks ? (missing index on referenced table ?)
	- functional index on a slow function ?
	- crummy hardware (5 MB/s RAID cards, etc)
	- too many indexes ?

> Next we have a select count(*) that  also one of the top offenders:
>
> select count(*) from public.tab3  where user_id=31
> and state='A'
> and amount>0;
>
>                                              QUERY PLAN
> -----------------------------------------------------------------------------------------------------
>  Aggregate  (cost=3836.53..3836.54 rows=1 width=0)
>    ->  Index Scan using order_user_indx ontab3 user_id   
> (cost=0.00..3834.29
> rows=897 width=0)
>          Index Cond: (idx_user_id = 31406948::numeric)
>          Filter: ((state = 'A'::bpchar) AND (amount > 0::numeric))
> (4 rows)
>
> We have an index on the user_id but not on the state or amount,
>
> add index to amount ?

	Can we see EXPLAIN ANALYZE ?

	In this case the ideal index would be multicolumn (user_id, state) or  
(user_id,amount) or (user_id,state,amount) but choosing between the 3  
depends on your data...

	You could do :

SELECT count(*), state, amount>0  FROM public.tab3  where user_id=31 GROUP  
BY state, amount>0;

	And post the results.

In response to

pgsql-performance by date

Next:From: Mario WeilguniDate: 2008-11-27 10:09:28
Subject: Re: performance tuning queries
Previous:From: A. KretschmerDate: 2008-11-27 06:12:26
Subject: Re: performance tuning queries

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