Thread: Index creation

On Mon, 24 May 2021 at 15:42, Yambu <hyambu@gmail.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

Thank you very helpful

On Mon, May 24, 2021 at 6:00 AM David Rowley <dgrowleyml@gmail.com> wrote:

On Mon, 24 May 2021 at 15:42, Yambu <hyambu@gmail.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

Just to add to David's great post, in my experience when I'm running tests with small datasets indices aren't used (b/c the query planner sees that row scans are faster) but if I run on a large dataset, the planner will use indices. I'm not sure if this applies to your testing setup..

Steve

On Sun, May 23, 2021 at 9:43 PM Yambu <hyambu@gmail.com> wrote:

Thank you very helpful

On Mon, May 24, 2021 at 6:00 AM David Rowley <dgrowleyml@gmail.com> wrote:

On Mon, 24 May 2021 at 15:42, Yambu <hyambu@gmail.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

Hi,

 

beside the other comments,  you may try to rework your query to reduce the OR clauses which rise the uncertainty for the planner.

 

 

e.g.:

 

SELECT

    *

FROM

    table_name

WHERE code_id IN ( 1, 2)

 

UNION

 

SELECT

    *

FROM

    table_name

WHERE  (code_id = 3 AND created_date < now())

LIMIT 1

 

 

 

From: Yambu [mailto:hyambu@gmail.com]
Sent: Montag, 24. Mai 2021 05:42
To: pgsql-sql@lists.postgresql.org
Subject: Index creation

 

hello people, I would like to create an index so that the query on here can use it,

 

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