From: Justin Pryzby <pryzby@telsasoft.com>
To: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
Cc: Nur Agus <nuragus.linux@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issue in PostgreSQL server...
Date: Sun, 5 Mar 2017 23:24:02 -0600
Message-ID: <20170306052402.GC16088@telsasoft.com> (raw)
In-Reply-To: <CAMkU=1wfOUvb1B32HZ3DwbW4TXVQaB+_1mMkpNyiDJGS=9HbfA@mail.gmail.com>
References: <0796d1d53d3e4aa692cfdc329f86b399@cyient.com>
<CAM7u+C7vUQ=U558JcGz83Sq2Z+wE2FEsup1hxBsmG5t3Kn08hw@mail.gmail.com>
<b513b675b75b452182628ca2667bf679@cyient.com>
<CAMkU=1wfOUvb1B32HZ3DwbW4TXVQaB+_1mMkpNyiDJGS=9HbfA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: pryzby@telsasoft.com, Dinesh.Chandra@cyient.com, nuragus.linux@gmail.com, jeff.janes@gmail.com
Subject: Re: Performance issue in PostgreSQL server...
In-Reply-To: <20170306052402.GC16088@telsasoft.com>
* 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