From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-performance@postgresql.org
Subject: Re: performance tuning queries
Date: Thu, 27 Nov 2008 07:12:26 +0100
Message-ID: <20081127061226.GA1087@a-kretschmer.de> (raw)
In-Reply-To: <200811262121.05463.kevin@kevinkempterllc.com>
References: <200811262121.05463.kevin@kevinkempterllc.com>
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
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: andreas.kretschmer@schollglas.com
Subject: Re: performance tuning queries
In-Reply-To: <20081127061226.GA1087@a-kretschmer.de>
* 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