From: | Wei Shan <weishan(dot)ang(at)gmail(dot)com> |
---|---|
To: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
Cc: | "pgsql-performance(at)postgresql(dot)org list" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Query not using Index |
Date: | 2016-03-27 16:12:43 |
Message-ID: | CAFe9ZTqrhz0h8EPspU=CUuk89i8k+HWf8igj+f5-47YCz+3aaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi Andreas,
The tablespace is not on SSD although I intend to do it within the next
week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
help.
On 26 March 2016 at 22:13, Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
wrote:
> Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:
>
> > Hi all,
> >
> > Please provide some advise on the following query not using the index:
> > I have 2 questions:
> >
> > 1. Why does the optimizer chose not to use the index when it will run
> faster?
>
> because of the estimated costs.:
>
> Seq Scan on testdb auditrecor0_ (cost=0.00..18147465.00
> Bitmap Heap Scan on testdb auditrecor0_ (cost=2291521.32..19046381.97
>
> The estimated costs for the index-scan are higher.
>
>
> > 2. How do I ensure the optimizer will use the index without setting
> > enable_seqscan='off'
>
> You have a dedicated tablespace for indexes, is this a SSD? You can try
> to reduce the random_page_cost, from default 4 to maybe 2.(depends on
> hardware) This would reduce the estimated costs for the Index-scan and
> prefer the index-scan.
>
>
>
> Regards, Andreas Kretschmer
> --
> Andreas Kretschmer
> http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Regards,
Ang Wei Shan
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2016-03-27 19:20:36 | Re: Query not using Index |
Previous Message | Andreas Kretschmer | 2016-03-26 14:13:46 | Re: Query not using Index |