From: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Nur Agus <nuragus.linux@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance issue in PostgreSQL server...
Date: Mon, 6 Mar 2017 12:17:22 +0000
Message-ID: <deb3e1c17e0643649354c1b2f97a9d6e@cyient.com> (raw)
In-Reply-To: <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>
<20170306052402.GC16088@telsasoft.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Dear Justin,
Below is the output of Query SELECT * FROM pg_stats WHERE tablename='point' AND attname='domain_class_id' ;
schemaname | tablename | attname | inherited | null_frac | avg_width | 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.66667e-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@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
________________________________
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 them. 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.
--
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: Dinesh.Chandra@cyient.com, pryzby@telsasoft.com, nuragus.linux@gmail.com, jeff.janes@gmail.com
Subject: Re: Performance issue in PostgreSQL server...
In-Reply-To: <deb3e1c17e0643649354c1b2f97a9d6e@cyient.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