From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | Yambu <hyambu(at)gmail(dot)com> |
Cc: | David Rowley <dgrowleyml(at)gmail(dot)com>, pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Index creation |
Date: | 2021-05-24 16:09:30 |
Message-ID: | CAJexoS+f_k3Qt=zgmHKK7Dz+7aCoG52A26+uMv4pO=HGR_u7AQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)gmail(dot)com> wrote:
> 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
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Yambu | 2021-05-24 17:34:21 | Re: Index creation |
Previous Message | Vijaykumar Jain | 2021-05-24 10:52:31 | Re: Long updates by primary key |