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:[email protected]]
Sent: 06 March, 2017 10:54 AM
To: Dinesh Chandra 12108 <[email protected]>
Cc: Nur Agus <[email protected]>; Jeff Janes <[email protected]>;
[email protected]
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
> <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
