agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedIndex creation
7+ messages / 4 participants
[nested] [flat]
* Index creation
@ 2021-05-24 03:41 Yambu <hyambu@gmail.com>
2021-05-24 04:00 ` Re: Index creation David Rowley <dgrowleyml@gmail.com>
2021-05-25 09:44 ` RE: Index creation Marc Mamin <M.Mamin@intershop.de>
0 siblings, 2 replies; 7+ messages in thread
From: Yambu @ 2021-05-24 03:41 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
hello people, I would like to create an index so that the query on here can
use it,
SELECT
*FROM
table_nameWHERE (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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index creation
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
@ 2021-05-24 04:00 ` David Rowley <dgrowleyml@gmail.com>
2021-05-24 04:42 ` Re: Index creation Yambu <hyambu@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David Rowley @ 2021-05-24 04:00 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index creation
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
2021-05-24 04:00 ` Re: Index creation David Rowley <dgrowleyml@gmail.com>
@ 2021-05-24 04:42 ` Yambu <hyambu@gmail.com>
2021-05-24 16:09 ` Re: Index creation Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 7+ messages in thread
From: Yambu @ 2021-05-24 04:42 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index creation
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
2021-05-24 04:00 ` Re: Index creation David Rowley <dgrowleyml@gmail.com>
2021-05-24 04:42 ` Re: Index creation Yambu <hyambu@gmail.com>
@ 2021-05-24 16:09 ` Steve Midgley <science@misuse.org>
2021-05-24 17:34 ` Re: Index creation Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Steve Midgley @ 2021-05-24 16:09 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index creation
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
2021-05-24 04:00 ` Re: Index creation David Rowley <dgrowleyml@gmail.com>
2021-05-24 04:42 ` Re: Index creation Yambu <hyambu@gmail.com>
2021-05-24 16:09 ` Re: Index creation Steve Midgley <science@misuse.org>
@ 2021-05-24 17:34 ` Yambu <hyambu@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Yambu @ 2021-05-24 17:34 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
i see that trend in small tables @Steve
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..
>
> 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
>>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Index creation
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
@ 2021-05-25 09:44 ` Marc Mamin <M.Mamin@intershop.de>
2021-05-25 10:18 ` Re: Index creation Yambu <hyambu@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Marc Mamin @ 2021-05-25 09:44 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index creation
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
2021-05-25 09:44 ` RE: Index creation Marc Mamin <M.Mamin@intershop.de>
@ 2021-05-25 10:18 ` Yambu <hyambu@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Yambu @ 2021-05-25 10:18 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Noted with thanks.
On Tue, May 25, 2021 at 9:44 AM Marc Mamin <M.Mamin@intershop.de> wrote:
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-05-25 10:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-24 03:41 Index creation Yambu <hyambu@gmail.com>
2021-05-24 04:00 ` David Rowley <dgrowleyml@gmail.com>
2021-05-24 04:42 ` Yambu <hyambu@gmail.com>
2021-05-24 16:09 ` Steve Midgley <science@misuse.org>
2021-05-24 17:34 ` Yambu <hyambu@gmail.com>
2021-05-25 09:44 ` Marc Mamin <M.Mamin@intershop.de>
2021-05-25 10:18 ` Yambu <hyambu@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox