From: PFC <lists@peufeu.com>
To: Kevin Kempter <kevink@consistentstate.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: performance tuning queries
Date: Thu, 27 Nov 2008 09:38:36 +0100
Message-ID: <op.uk9rmmqncigqcu@soyouz> (raw)
In-Reply-To: <200811262121.05463.kevin@kevinkempterllc.com>
References: <200811262121.05463.kevin@kevinkempterllc.com>
> 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.
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, kevink@consistentstate.com
Subject: Re: performance tuning queries
In-Reply-To: <op.uk9rmmqncigqcu@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