agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feed(no subject)
5+ messages / 4 participants
[nested] [flat]
* (no subject)
@ 2022-02-01 08:42 Дмитрий Воронин <carriingfate92@yandex.ru>
0 siblings, 1 reply; 5+ messages in thread
From: Дмитрий Воронин @ 2022-02-01 08:42 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
<div>Hi all,</div><div> </div><div>I'm using PostgreSQL 13.</div><div> </div><div>I have a table:</div><div> </div><div>CREATE TABLE test(docid integer, jsonb attrs);</div><div> </div><div>So, attrs contains data like</div><div> </div><div>...</div><div>"dates": ["2019-10-02", "2018-02-03"]</div><div>...</div><div> </div><div>So, I want to SELECT all docids, which dates in range:</div><div> </div><div><div>SELECT attrs FROM document_resinfo WHERE attrs @? '$.dates[*].datetime() ? (@ >= "2020-10-02".datetime())';</div><div> </div><div>How can I create index on attrs field to query docids with other date? Thanks.</div></div><div> </div><div>-- <br />Best regards, Dmitry Voronin</div><div> </div>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
@ 2022-02-01 16:15 Steve Midgley <science@misuse.org>
parent: Дмитрий Воронин <carriingfate92@yandex.ru>
0 siblings, 2 replies; 5+ messages in thread
From: Steve Midgley @ 2022-02-01 16:15 UTC (permalink / raw)
To: Дмитрий Воронин <carriingfate92@yandex.ru>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Tue, Feb 1, 2022 at 12:42 AM Дмитрий Воронин <carriingfate92@yandex.ru>
wrote:
> Hi all,
>
> I'm using PostgreSQL 13.
>
> I have a table:
>
> CREATE TABLE test(docid integer, jsonb attrs);
>
> So, attrs contains data like
>
> ...
> "dates": ["2019-10-02", "2018-02-03"]
> ...
>
> So, I want to SELECT all docids, which dates in range:
>
> SELECT attrs FROM document_resinfo WHERE attrs @? '$.dates[*].datetime() ?
> (@ >= "2020-10-02".datetime())';
>
> How can I create index on attrs field to query docids with other date?
> Thanks.
>
>
Have you tried just putting a default index on that column? I think it
should work fine.
CREATE INDEX attrs_idx ON test (attrs)
IIRC, jsonb can be indexed like any other column and you get significant
performance benefits when using the index. Also IIRC, you can index
"deeper" into jsonb if you only want to index part of the jsonb structure -
which is more efficient, so you don't index a bunch of elements that you
never search.
Have you tried this approach? What problems are you experiencing?
Steve
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
@ 2022-02-01 20:10 Michael Lewis <mlewis@entrata.com>
parent: Steve Midgley <science@misuse.org>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Lewis @ 2022-02-01 20:10 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Дмитрий Воронин <carriingfate92@yandex.ru>; pgsql-sql@lists.postgresql.org
On Tue, Feb 1, 2022, 9:16 AM Steve Midgley <science@misuse.org> wrote:
>
>
> On Tue, Feb 1, 2022 at 12:42 AM Дмитрий Воронин <carriingfate92@yandex.ru>
> wrote:
>
>>
>> SELECT attrs FROM document_resinfo WHERE attrs @? '$.dates[*].datetime()
>> ? (@ >= "2020-10-02".datetime())';
>>
>> How can I create index on attrs field to query docids with other date?
>> Thanks.
>>
>>
>
> Have you tried just putting a default index on that column? I think it
> should work fine.
>
> CREATE INDEX attrs_idx ON test (attrs)
>
You'd want to use gin index type. Btree won't be very helpful.
I would consider a btree on the jsonb key itself.
One major hesitation I have with using jsonb much is that you don't get
statistics on the distribution of data (ndistinct, mcv, histogram, etc).
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
@ 2022-02-02 05:38 Дмитрий Воронин <carriingfate92@yandex.ru>
parent: Steve Midgley <science@misuse.org>
1 sibling, 1 reply; 5+ messages in thread
From: Дмитрий Воронин @ 2022-02-02 05:38 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Hi, Steve.<br /><br />19:15, 1 февраля 2022 г., Steve Midgley <science@misuse.org>:<br /><blockquote class="210e7a848e8fcb45wmi-quote"><div dir="ltr"><div class="f13ca48719c8a60033905b23b39675agmail_quote"><div class="334d7d341e3233c5b27ca91297445127gmail_attr" dir="ltr">On Tue, Feb 1, 2022 at 12:42 AM Дмитрий Воронин <<a href="mailto:carriingfate92@yandex.ru">carriingfate92@yandex.ru</a>> wrote:<br /></div><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><div>Hi all,</div><div> </div><div>I'm using PostgreSQL 13.</div><div> </div><div>I have a table:</div><div> </div><div>CREATE TABLE test(docid integer, jsonb attrs);</div><div> </div><div>So, attrs contains data like</div><div> </div><div>...</div><div>"dates": ["<span class="1f1ea193f6735cf0wmi-callto">2019-10-02</span>", "<span class="1f1ea193f6735cf0wmi-callto">2018-02-03</span>"]</div><div>...</div><div> </div><div>So, I want to SELECT all docids, which dates in range:</div><div> </div><div><div>SELECT attrs FROM document_resinfo WHERE attrs @? '$.dates[*].datetime() ? (@ >= "<span class="1f1ea193f6735cf0wmi-callto">2020-10-02</span>".datetime())';</div><div> </div><div>How can I create index on attrs field to query docids with other date? Thanks.</div></div><div> </div></blockquote><div><br /></div><div>Have you tried just putting a default index on that column? I think it should work fine.</div><div><br /></div><div>CREATE INDEX attrs_idx ON test (attrs)</div></div></div></blockquote><div><br /></div><div>Yes, I do. This speeds up search on first-levels keys on queries like:</div><div><br /></div><div>SELECT docid FROM test WHERE attrs @> '{"kw": ["a", "b"]}'</div><br /><blockquote class="210e7a848e8fcb45wmi-quote"><div dir="ltr"><div class="f13ca48719c8a60033905b23b39675agmail_quote"><div>IIRC, jsonb can be indexed like any other column and you get significant performance benefits when using the index. Also IIRC, you can index "deeper" into jsonb if you only want to index part of the jsonb structure - which is more efficient, so you don't index a bunch of elements that you never search.</div></div></div></blockquote><div><br /></div>Yes, I known that.<br /><blockquote class="210e7a848e8fcb45wmi-quote"><div dir="ltr"><div class="f13ca48719c8a60033905b23b39675agmail_quote"><div></div><div>Have you tried this approach? What problems are you experiencing?</div><div></div></div></div></blockquote><div><br /></div><div>Yes, I already create another index on dates key. I think because jsonpath type does not have good index support, I will give sequential scan on this table. </div><div><br /></div><div>So, searching on dates field will be often and I want speed up by indexing but I don't known how.</div><br /><blockquote class="210e7a848e8fcb45wmi-quote"><div dir="ltr"><div class="f13ca48719c8a60033905b23b39675agmail_quote"><div>Steve</div></div></div>
</blockquote><br /><br />Best regards, Dmitry Voronin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
@ 2022-02-02 05:47 David G. Johnston <david.g.johnston@gmail.com>
parent: Дмитрий Воронин <carriingfate92@yandex.ru>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2022-02-02 05:47 UTC (permalink / raw)
To: Дмитрий Воронин <carriingfate92@yandex.ru>; +Cc: Steve Midgley <science@misuse.org>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Tuesday, February 1, 2022, Дмитрий Воронин <carriingfate92@yandex.ru>
wrote:
>
>
> ...
> "dates": ["2019-10-02", "2018-02-03"]
> ...
>
> So, I want to SELECT all docids, which dates in range:
>
>
>
> So, searching on dates field will be often and I want speed up by indexing
> but I don't known how.
>
Create a generated column of type daterange and populate that on
insert/update. Index that column. Write queries against that column. (Not
tested, but in short get rid of the json pseudo daterange array
implementation and use the real SQL daterange type)
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-02-02 05:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-01 08:42 Дмитрий Воронин <carriingfate92@yandex.ru>
2022-02-01 16:15 ` Re: Steve Midgley <science@misuse.org>
2022-02-01 20:10 ` Re: Michael Lewis <mlewis@entrata.com>
2022-02-02 05:38 ` Re: Дмитрий Воронин <carriingfate92@yandex.ru>
2022-02-02 05:47 ` Re: David G. Johnston <david.g.johnston@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