On Mon, May 24, 2021 at 6:09 PM Steve Midgley <science@misuse.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..
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].
