Date: Sun, 5 Mar 2017 23:24:02 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
Cc: Nur Agus <nuragus.linux@gmail.com>, Jeff Janes <jeff.janes@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Performance issue in PostgreSQL server...
Message-ID: <20170306052402.GC16088@telsasoft.com>
References: <0796d1d53d3e4aa692cfdc329f86b399@cyient.com>
 <CAM7u+C7vUQ=U558JcGz83Sq2Z+wE2FEsup1hxBsmG5t3Kn08hw@mail.gmail.com>
 <b513b675b75b452182628ca2667bf679@cyient.com>
 <CAMkU=1wfOUvb1B32HZ3DwbW4TXVQaB+_1mMkpNyiDJGS=9HbfA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMkU=1wfOUvb1B32HZ3DwbW4TXVQaB+_1mMkpNyiDJGS=9HbfA@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Sun, Mar 05, 2017 at 08:23:08PM -0800, Jeff Janes wrote:
> On Fri, Mar 3, 2017 at 4:44 AM, Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com> wrote:
> > The below is the output for psql=> EXPLAIN ANALYZE SELECT DISTINCT
> > feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence
> > oe ON p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (11) AND
> > (p.modification_time > '2015-05-10 00:06:56.056 IST' OR
> > oe.modification_time > '2015-05-10 00:06:56.056 IST') ORDER BY feature_id
> ...
> 
> >                      ->  Index Scan using point_domain_class_id_index on point p  (cost=0.00..1483472.70 rows=1454751 width=16) (actual time=27.265..142101.1 59 rows=1607491 loops=1)
> >                            Index Cond: (domain_class_id = 11)
> 
> Why wouldn't this be using a bitmap scan rather than a regular index scan?
> It seems like it should prefer the bitmap scan, unless the table is well
> clustered on domain_class_id.  In which case, why isn't it just faster?

Could you send:

SELECT * FROM pg_stats WHERE tablename='point' AND attname='domain_class_id' ;

.. or if that's too verbose or you don't want to share the histogram or MCV
list:

SELECT correlation FROM pg_stats WHERE tablename='point' AND attname='domain_class_id' ;

Justin


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance