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

Re: performance tuning queries

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: performance tuning queries
Date: 2008-11-27 06:12:26
Message-ID: 20081127061226.GA1087@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-performance
am  Wed, dem 26.11.2008, um 21:21:04 -0700 mailte Kevin Kempter folgendes:
> 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 ?

Depends.

- Is the index on user_id a unique index?
- how many different values are in the table for state, i.e., maybe an
  index on state can help
- how many rows in the table with amount > 0? If almost all rows
  contains an amount > 0 an index can't help in this case


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

In response to

pgsql-performance by date

Next:From: PFCDate: 2008-11-27 08:38:36
Subject: Re: performance tuning queries
Previous:From: Kevin KempterDate: 2008-11-27 04:21:04
Subject: performance tuning queries

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