public inbox for [email protected]help / color / mirror / Atom feed
Re: A way to optimize sql about the last temporary-related row 3+ messages / 2 participants [nested] [flat]
* Re: A way to optimize sql about the last temporary-related row @ 2024-06-27 15:27 Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ron Johnson @ 2024-06-27 15:27 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Thu, Jun 27, 2024 at 11:20 AM [email protected] <[email protected]> wrote: [snip] > -- insert 4M records > insert into test_table(pk_id) select generate_series(1,4000000,1); > > -- now set some random data, distribuited between specific ranges (as in > my production table) > update test_table set > datetime_field_1 = timestamp '2000-01-01 00:00:00' + random() * > (timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01 00:00:00'), > integer_field_1 = floor(random() * (6-1+1) + 1)::int, > integer_field_2 = floor(random() * (200000-1+1) + 1)::int; > > > -- indexes > CREATE INDEX idx_test_table_integer_field_1 ON test_table(integer_field_1); > CREATE INDEX xtest_table_datetime_field_1 ON test_table(datetime_field_1 > desc); > CREATE INDEX idx_test_table_integer_field_2 ON test_table(integer_field_2); > > Off-topic: save some resources by vacuuming before creating indices. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A way to optimize sql about the last temporary-related row @ 2024-06-27 15:33 [email protected] <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: [email protected] @ 2024-06-27 15:33 UTC (permalink / raw) To: [email protected] Hi, You are right. Too quickly copy-paste on my part :-) I take this opportunity to add a NOT insignificant detail. Before executing the select query I clear the cache: systemctl stop postgresql-16 && sync && echo 3 > /proc/sys/vm/drop_caches && systemctl start postgresql-16 I need to get a performance result even if data is not in cache. My best regards, Agharta Il 27/06/24 5:27 PM, Ron Johnson ha scritto: > On Thu, Jun 27, 2024 at 11:20 AM [email protected] > <[email protected]> wrote: > [snip] > > -- insert 4M records > insert into test_table(pk_id) select generate_series(1,4000000,1); > > -- now set some random data, distribuited between specific ranges > (as in > my production table) > update test_table set > datetime_field_1 = timestamp '2000-01-01 00:00:00' + random() * > (timestamp '2024-05-31 23:59:59' - timestamp '2000-01-01 00:00:00'), > integer_field_1 = floor(random() * (6-1+1) + 1)::int, > integer_field_2 = floor(random() * (200000-1+1) + 1)::int; > > > -- indexes > CREATE INDEX idx_test_table_integer_field_1 ON > test_table(integer_field_1); > CREATE INDEX xtest_table_datetime_field_1 ON > test_table(datetime_field_1 > desc); > CREATE INDEX idx_test_table_integer_field_2 ON > test_table(integer_field_2); > > > Off-topic: save some resources by vacuuming before creating indices. > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A way to optimize sql about the last temporary-related row @ 2024-06-27 15:43 Ron Johnson <[email protected]> parent: [email protected] <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Ron Johnson @ 2024-06-27 15:43 UTC (permalink / raw) To: pgsql-general On Thu, Jun 27, 2024 at 11:33 AM [email protected] <[email protected]> wrote: > Hi, > > You are right. Too quickly copy-paste on my part :-) > > I take this opportunity to add a NOT insignificant detail. > > Before executing the select query I clear the cache: > > systemctl stop postgresql-16 && sync && echo 3 > /proc/sys/vm/drop_caches > && systemctl start postgresql-16 > > I need to get a performance result even if data is not in cache. > In addition to David's suggestion, consider the pg_prewarm extension: https://www.postgresql.org/docs/current/pgprewarm.html ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-27 15:43 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-27 15:27 Re: A way to optimize sql about the last temporary-related row Ron Johnson <[email protected]> 2024-06-27 15:33 ` [email protected] <[email protected]> 2024-06-27 15:43 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox