From: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
To: Justin Pryzby <pryzby@telsasoft.com>
CC: Nur Agus <nuragus.linux@gmail.com>, Jeff Janes <jeff.janes@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Performance issue in PostgreSQL server...
Thread-Topic: [PERFORM] Performance issue in PostgreSQL server...
Thread-Index: AdKUCRR1R30VhxuMT2utF4ZoBbTRiP//w5kA//+eX3CABJJiAIAAEQQA//8xNEA=
Date: Mon, 6 Mar 2017 12:17:22 +0000
Message-ID: <deb3e1c17e0643649354c1b2f97a9d6e@cyient.com>
References: <0796d1d53d3e4aa692cfdc329f86b399@cyient.com>
 <CAM7u+C7vUQ=U558JcGz83Sq2Z+wE2FEsup1hxBsmG5t3Kn08hw@mail.gmail.com>
 <b513b675b75b452182628ca2667bf679@cyient.com>
 <CAMkU=1wfOUvb1B32HZ3DwbW4TXVQaB+_1mMkpNyiDJGS=9HbfA@mail.gmail.com>
 <20170306052402.GC16088@telsasoft.com>
In-Reply-To: <20170306052402.GC16088@telsasoft.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Dear Justin,

Below is the output of Query SELECT * FROM pg_stats WHERE tablename=3D'poin=
t' AND attname=3D'domain_class_id' ;


schemaname | tablename |     attname     | inherited | null_frac | avg_widt=
h | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | =
correlation

"evidence"|"point"|"domain_class_id"|f|0|8|10|"{7,9,2,11,43,3,1,10,4,17}"|"=
{0.9322,0.0451333,0.0145,0.00393333,0.00183333,0.00146667,0.0005,0.0003,6.6=
6667e-05,6.66667e-05}"|""|0.889078


Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.


-----Original Message-----
From: Justin Pryzby [mailto:pryzby@telsasoft.com]
Sent: 06 March, 2017 10:54 AM
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: [PERFORM] Performance issue in PostgreSQL server...

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@cyie=
nt.com> wrote:
> > The below is the output for psql=3D> EXPLAIN ANALYZE SELECT DISTINCT
> > feature_id FROM evidence.point p INNER JOIN
> > evidence.observation_evidence oe ON p.feature_id =3D 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 o=
n point p  (cost=3D0.00..1483472.70 rows=3D1454751 width=3D16) (actual time=
=3D27.265..142101.1 59 rows=3D1607491 loops=3D1)
> >                            Index Cond: (domain_class_id =3D 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 fast=
er?

Could you send:

SELECT * FROM pg_stats WHERE tablename=3D'point' AND attname=3D'domain_clas=
s_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=3D'point' AND attname=3D'd=
omain_class_id' ;

Justin

________________________________

DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may=
 contain confidential and privileged information. Any unauthorized review, =
use, disclosure or distribution is prohibited. If you are not the intended =
recipient, please contact the sender by reply email and destroy all copies =
of the original message. Check all attachments for viruses before opening t=
hem. All views or opinions presented in this e-mail are those of the author=
 and may not reflect the opinion of Cyient or those of our affiliates.


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