Re: Index creation

i see that trend in small tables @Steve

On Mon, May 24, 2021 at 6:09 PM Steve Midgley <science(at)misuse(dot)org> wrote:

> 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
>>>
>>