Re: Index creation

Thank you very helpful

On Mon, May 24, 2021 at 6:00 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Mon, 24 May 2021 at 15:42, Yambu <hyambu(at)gmail(dot)com> wrote:
> > SELECT
> > *
> > FROM
> > table_name
> > WHERE (code_id = 1
> > OR code_id = 2
> > OR (code_id = 3
> > AND created_date < now()))
> >
> > LIMIT 1;
> >
> > please advise me on how I should create index. I created index on
> code_id but it's not being used
>
> [1] might be relevant to you. An index on code_id should be usable for
> the query by using Bitmap Index Scans then Bitmap ORing the results
> from the 3 individual scans.
>
> If you want to confirm that the index can be used, then you could try
> running the query after doing: SET enable_seqscan TO off;. While
> you're there, if the index is used you could check if the query became
> any faster as a result. If it did not, then the planner did a good job
> not to use the index. If it became faster, then you might want to look
> into making adjustments to effective_cache_size and/or
> random_page_cost [2].
>
> David
>
> [1]
> https://wiki.postgresql.org/wiki/FAQ#Why_are_my_queries_slow.3F_Why_don.27t_they_use_my_indexes.3F
> [2] https://www.postgresql.org/docs/current/runtime-config-query.html
>