Re: When are largobject records TOASTed into pg_toast_2613?
Hello all,
You can find at the end of this email, a new version of the script that I
use to remove the TOAST table on pg_largobject catalog table.
I fixed some typos and wrong synthaxes that I had typed too quickly in my
first version.
Thanks to this script, I can migrate successfully the PostgreSQL instance.
Yet, the `pg_largobject` table is still considered TOASTed.
I have the following behaviour:
```sql
---Using the pg_largeobject_loid_pn_index is OK:
SELECT loid from pg_largeobject order by loid desc limit 5;
loid
--
47232219
47232219
47232219
47232219
47232219
(5 rows)
--- according to pg_class, pg_largobject is not TOASTed anymore:
SELECT oid, relname, relnamespace, relfilenode, reltoastrelid, relpages,
(relpages*8/1024)::int as mb_size, reltuples::int, relkind
from pg_class
where relname like 'pg_toast_2613%'
or relname like 'pg_largeobject%' order by relname;
oid | relname | relnamespace | relfilenode |
reltoastrelid | relpages | mb_size | reltuples | relkind
--+---+--+-+---+--+-+---+-
2613 | pg_largeobject| 11 |47237561 |
0 | 8791 | 68 |727520 | r
2683 | pg_largeobject_loid_pn_index | 11 |47237567 |
0 | 1997 | 15 |727520 | i
2995 | pg_largeobject_metadata | 11 |2995 |
0 | 230 | 1 | 5071 | r
2996 | pg_largeobject_metadata_oid_index | 11 |2996 |
0 | 2320 | 18 | 5071 | i
(4 rows)
--- But the pg_largeobject table is not accessible:
SELECT * from pg_largeobject order by loid desc limit 5;
ERROR: could not open relation with OID 16619
--- Same error when using largeobject functions:
SELECT lo_get(47232219);
ERROR: could not open relation with OID 16619
--- No TOAST reference into pg_depend for pg_largobject
SELECT * from pg_depend where 2613 in (objsubid, refobjid);
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
-+---+--++--+-+-
0 | 0 |0 | 1259 | 2613 | 0 | p
--- As for OID 16619
SELECT * from pg_depend where 16619 in (objsubid, refobjid);
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
-+---+--++--+-+-
```
> Is there another catalog table where the TOAST reference can be located?
Thanks in advance for your help,
Have a nice Sunday,
Thomas
Latest version of the script:
```sql
#!/usr/bin/env bash
#
set -euo pipefail
database_name="xxx"
postgresql_conf_file="/yyy/postgresql.conf"
# Define log files
LOG_FOLDER="/zzz/log"
mkdir -p "${LOG_FOLDER}"
LOG_REMOVE="${LOG_FOLDER}/remove_operation.log"
# Step 1: check if table pg_toast_2613 exists
toast_count="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select count(*) from pg_class where relname = 'pg_toast_2613';")"
echo -e "TOAST exists ::${toast_count}" | tee -a "${LOG_REMOVE}"
if [[ "${toast_count}" == "1" ]]; then
# Step 2: Check if table pg_toast_2613 has rows and pages
toast_pages="$(psql -U postgres --dbname=${database_name} -At --no-psqlrc
-c "select relpages from pg_class where relname = 'pg_toast_2613';")"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples::int from pg_class where relname =
'pg_toast_2613';")"
echo -e "TOAST content pages ::${toast_pages}:: tuples ::${toast_tuples}"
| tee -a "${LOG_REMOVE}"
# Step 3 OPTIONAL: vacuum full pg_largeobject if needed
if [[ "${toast_tuples}" -gt "0" ]]; then
echo -e "Start of vacuum" | tee -a "${LOG_REMOVE}"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "VACUUM
FULL ANALYZE VERBOSE pg_largeobject;" 2>&1 | tee -a "${LOG_REMOVE}"
echo -e "End of vacuum" | tee -a "${LOG_REMOVE}"
## After VACUUM post-check
toast_pages="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select relpages from pg_class where relname =
'pg_toast_2613';")"
toast_tuples="$(psql -U postgres --dbname=${database_name} -At
--no-psqlrc -c "select reltuples::int from pg_class where relname =
'pg_toast_2613';")"
echo -e "TOAST content pages ::${toast_pages}:: tuples
::${toast_tuples}" | tee -a "${LOG_REMOVE}"
fi
# Step 4: Remove TOAST information for pg_largeobject into pg_class
echo -e "Remove TOAST on pg_largeobject" | tee -a "${LOG_REMOVE}"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET reltoastrelid = 0 WHERE oid = 2613;" | tee -a "${LOG_REMOVE}"
# Step 5: Drop pg_toast_2613% objects
echo -e "Change pg_toast_2613 type to relation" | tee -a "${LOG_REMOVE}"
psql -U postgres --dbname=${database_name} -At --no-psqlrc -c "UPDATE
pg_class SET relkind = 'r' WHERE relname =
Re: Performance of "distinct with limit"
Thanks for your insight Jeff. Interesting read! K Sent with [ProtonMail](https://protonmail.com) Secure Email. ‐‐‐ Original Message ‐‐‐ On Saturday, August 29, 2020 6:23 PM, Jeff Janes wrote: > On Fri, Aug 28, 2020 at 8:34 AM Klaudie Willis > wrote: > >> No index on n, no. Index might solve it yes, but it seems to me such a >> trivial optimization even without. Obviously it is not. >> >> QUERY PLAN | >> --| >> Limit (cost=1911272.10..1911272.12 rows=2 width=7) | >> -> HashAggregate (cost=1911272.10..1911282.45 rows=1035 width=7) | >> Group Key: cfi | >> -> Seq Scan on bigtable (cost=0.00..1817446.08 rows=37530408 width=7)| > > I think it would be nice if the LIMIT functionality could be pushed down into > the HashAgg so it could stop early, I've run into this a few times. But it > just isn't implemented. It wouldn't be the hardest feature to ever add to > PostgreSQL, but it also wouldn't be trivial. It would require coordinated > changes both to the planner and to the executor. > > Also, the use of LIMIT without an ORDER BY makes the query non-deterministic, > which makes it kind of a second-class citizen. There might be more enthusiasm > among experienced developers for implementing this if it weren't for that. > (Although there may be related deterministic cases in which a similar limited > hash agg could be useful.) > > In the meantime, an index on "n" would probably cause it to switch to a > Unique plan which reads in index order. This plan does get to stop early. > > Cheers, > > Jeff
Re: Performance of "distinct with limit"
On Fri, Aug 28, 2020 at 8:34 AM Klaudie Willis < klaudie.wil...@protonmail.com> wrote: > No index on n, no. Index might solve it yes, but it seems to me such a > trivial optimization even without. Obviously it is not. > > QUERY > PLAN > | > > --| > Limit (cost=1911272.10..1911272.12 rows=2 > width=7) | > -> HashAggregate (cost=1911272.10..1911282.45 rows=1035 > width=7) | > Group Key: > cfi| > -> Seq Scan on bigtable (cost=0.00..1817446.08 rows=37530408 > width=7)| > > I think it would be nice if the LIMIT functionality could be pushed down into the HashAgg so it could stop early, I've run into this a few times. But it just isn't implemented. It wouldn't be the hardest feature to ever add to PostgreSQL, but it also wouldn't be trivial. It would require coordinated changes both to the planner and to the executor. Also, the use of LIMIT without an ORDER BY makes the query non-deterministic, which makes it kind of a second-class citizen. There might be more enthusiasm among experienced developers for implementing this if it weren't for that. (Although there may be related deterministic cases in which a similar limited hash agg could be useful.) In the meantime, an index on "n" would probably cause it to switch to a Unique plan which reads in index order. This plan does get to stop early. Cheers, Jeff
Re: How to properly query lots of rows based on timestamps?
=?utf-8?Q?Thorsten_Sch=C3=B6ning?= writes:
> I have a table containing around 95 million rows, pretty much only
> storing a timestamp and further IDs of related tables containing the
> actual data in the end.
>> CREATE TABLE clt_rec
>> (
>> id BIGSERIAL NOT NULL,
>> oms_rec BIGINT NOT NULL,
>> captured_at TIMESTAMP(6) WITH TIME ZONE NOT NULL,
>> rssiSMALLINT NOT NULL,
>> CONSTRAINT pk_clt_rec PRIMARY KEY (id),
>> CONSTRAINT fk_clt_rec_oms_rec FOREIGN KEY (oms_rec) REFERENCES "oms_rec"
>> ("id"),
>> CONSTRAINT uk_clt_rec_oms_rec UNIQUE (oms_rec)
>> );
TBH, this seems like a pretty awful data design. If you included the
timestamp column into oms_rec, and had an index on it, then you would
not need a join at all.
> Postgres seems to properly use available indexes, parallel workers and
> stuff like that. But looking at the actual times and compared to all
> the other parts of the query, comparing those timestamps simply takes
> the most time.
Timestamp comparison reduces to comparison of int64's, so it's
hard to imagine that anything could be much cheaper. The part of
your plan that is actually eating the most cycles is the repeated
index probes into oms_rec:
>> -> Index Scan using pk_oms_rec on oms_rec (cost=0.57..7.39 rows=1
>> width=12) (actual time=0.002..0.002 rows=1 loops=1003394)
.002 * 1003394 = 2006.788, which of course can't be because the
whole query took 911 ms; but there's not much accuracy in this
per-loop measurement. In any case, the 155 msec spent scanning
clt_rec would be entirely unnecessary if the timestamp existed in
oms_rec. We can also bet that the index traversal costs would be
quite a bit less: in this query, we are re-descending pk_oms_rec
from the root, 1003394 times, which is a lot of cycles that wouldn't
be needed with a single scan on a timestamp index.
regards, tom lane
Re: How to properly query lots of rows based on timestamps?
> On 29 Aug 2020, at 10:24, Thorsten Schöning wrote:
>
> Hi all,
>
> I have a table containing around 95 million rows, pretty much only
> storing a timestamp and further IDs of related tables containing the
> actual data in the end.
>
>> CREATE TABLE clt_rec
>> (
>> id BIGSERIAL NOT NULL,
>> oms_rec BIGINT NOT NULL,
>> captured_at TIMESTAMP(6) WITH TIME ZONE NOT NULL,
>> rssiSMALLINT NOT NULL,
>> CONSTRAINT pk_clt_rec PRIMARY KEY (id),
>> CONSTRAINT fk_clt_rec_oms_rec FOREIGN KEY (oms_rec) REFERENCES "oms_rec"
>> ("id"),
>> CONSTRAINT uk_clt_rec_oms_rec UNIQUE (oms_rec)
>> );
>
> In many use cases I need to search all of those rows based on their
> timestamp to find rows arbitrary in the past: Sometimes it's only 15
> minutes into the past, sometimes it's 2 years, sometimes it's finding
> the first day of each month over 15 months for some of those telegrams
> etc. In the end, I pretty often need to compare those timestamps and
> some queries simply take multiple seconds in the end, especially
> adding up if multiple, but slightly different queries need to be
> executed one after another. The following are two abstracts of
> Postgres' query plans:
>
> Plan 1:
>
>> -> Nested Loop (cost=1.14..343169.49 rows=43543 width=20) (actual
>> time=0.313..113.974 rows=34266 loops=3)
>>-> Parallel Index Scan using idx_clt_rec_captured_at on clt_rec
>> (cost=0.57..3437.90 rows=43543 width=24) (actual time=0.153..20.192
>> rows=34266 loops=3)
>>Index Cond: ((captured_at >= ('2020-08-01 00:00:00+02'::timestamp
>> with time zone - '00:00:00'::interval)) AND (captured_at <= ('2020-08-01
>> 00:00:00+02'::timestamp with time zone + '1 day'::interval)))
>>-> Index Scan using pk_oms_rec on oms_rec (cost=0.57..7.80 rows=1
>> width=12) (actual time=0.002..0.002 rows=1 loops=102799)
>>Index Cond: (id = clt_rec.oms_rec)
What happens here is that the planner looks up the lower and upper boundaries,
everything in between those index nodes is a candidate record. Next, it loops
over those to match the other condition of your query (id = clt_rec.oms_rec).
You didn’t tell whether there’s an index on that column.
You’d probably see a performance improvement were you to create an index on
(captured_at, id). If your Postgres version is somewhat recent, that could even
lead to an Index Only Scan.
> Plan 2:
>
>> -> Nested Loop (cost=1.14..836381.50 rows=111934 width=20) (actual
>> time=0.379..911.697 rows=334465 loops=3)
>>-> Parallel Index Scan using idx_clt_rec_captured_at on clt_rec
>> (cost=0.57..8819.57 rows=111934 width=24) (actual time=0.193..154.176
>> rows=334465 loops=3)
>>Index Cond: ((captured_at >= ('2020-08-28 10:21:06.968+02'::timestamp
>> with time zone - '14 days'::interval)) AND (captured_at <= ('2020-08-28
>> 10:21:06.968+02'::timestamp with time zone + '00:00:00'::interval)))
>>-> Index Scan using pk_oms_rec on oms_rec (cost=0.57..7.39 rows=1
>> width=12) (actual time=0.002..0.002 rows=1 loops=1003394)
>>Index Cond: (id = clt_rec.oms_rec)
And this situation is very much the same issue, apart from the larger number of
candidate records.
> Postgres seems to properly use available indexes, parallel workers and
> stuff like that. But looking at the actual times and compared to all
> the other parts of the query, comparing those timestamps simply takes
> the most time.
It only needs to compare 2 timestamps.
> I've looked into this topic and found statements about that one
> shouldn't put too many rows into the index[1] and stuff like that or
> it will be ignored at all. But that doesn't seem to be the case for me
> according to the plan. OTOH, my index really simply is about the
> column containing the timestamp, no function reducing things to dates
> or stuff like that to reduce the number of rows.
>
>> CREATE INDEX idx_clt_rec_captured_at ON clt_rec USING btree ( captured_at );
Try this:
CREATE INDEX idx_clt_rec_captured_at ON clt_rec USING btree ( captured_at, id );
Alban Hertroys
--
There is always an exception to always.
How to properly query lots of rows based on timestamps?
Hi all,
I have a table containing around 95 million rows, pretty much only
storing a timestamp and further IDs of related tables containing the
actual data in the end.
> CREATE TABLE clt_rec
> (
> id BIGSERIAL NOT NULL,
> oms_rec BIGINT NOT NULL,
> captured_at TIMESTAMP(6) WITH TIME ZONE NOT NULL,
> rssiSMALLINT NOT NULL,
> CONSTRAINT pk_clt_rec PRIMARY KEY (id),
> CONSTRAINT fk_clt_rec_oms_rec FOREIGN KEY (oms_rec) REFERENCES "oms_rec"
> ("id"),
> CONSTRAINT uk_clt_rec_oms_rec UNIQUE (oms_rec)
> );
In many use cases I need to search all of those rows based on their
timestamp to find rows arbitrary in the past: Sometimes it's only 15
minutes into the past, sometimes it's 2 years, sometimes it's finding
the first day of each month over 15 months for some of those telegrams
etc. In the end, I pretty often need to compare those timestamps and
some queries simply take multiple seconds in the end, especially
adding up if multiple, but slightly different queries need to be
executed one after another. The following are two abstracts of
Postgres' query plans:
Plan 1:
> -> Nested Loop (cost=1.14..343169.49 rows=43543 width=20) (actual
> time=0.313..113.974 rows=34266 loops=3)
> -> Parallel Index Scan using idx_clt_rec_captured_at on clt_rec
> (cost=0.57..3437.90 rows=43543 width=24) (actual time=0.153..20.192
> rows=34266 loops=3)
> Index Cond: ((captured_at >= ('2020-08-01 00:00:00+02'::timestamp
> with time zone - '00:00:00'::interval)) AND (captured_at <= ('2020-08-01
> 00:00:00+02'::timestamp with time zone + '1 day'::interval)))
> -> Index Scan using pk_oms_rec on oms_rec (cost=0.57..7.80 rows=1
> width=12) (actual time=0.002..0.002 rows=1 loops=102799)
> Index Cond: (id = clt_rec.oms_rec)
Plan 2:
> -> Nested Loop (cost=1.14..836381.50 rows=111934 width=20) (actual
> time=0.379..911.697 rows=334465 loops=3)
> -> Parallel Index Scan using idx_clt_rec_captured_at on clt_rec
> (cost=0.57..8819.57 rows=111934 width=24) (actual time=0.193..154.176
> rows=334465 loops=3)
> Index Cond: ((captured_at >= ('2020-08-28 10:21:06.968+02'::timestamp
> with time zone - '14 days'::interval)) AND (captured_at <= ('2020-08-28
> 10:21:06.968+02'::timestamp with time zone + '00:00:00'::interval)))
> -> Index Scan using pk_oms_rec on oms_rec (cost=0.57..7.39 rows=1
> width=12) (actual time=0.002..0.002 rows=1 loops=1003394)
> Index Cond: (id = clt_rec.oms_rec)
Postgres seems to properly use available indexes, parallel workers and
stuff like that. But looking at the actual times and compared to all
the other parts of the query, comparing those timestamps simply takes
the most time.
I've looked into this topic and found statements about that one
shouldn't put too many rows into the index[1] and stuff like that or
it will be ignored at all. But that doesn't seem to be the case for me
according to the plan. OTOH, my index really simply is about the
column containing the timestamp, no function reducing things to dates
or stuff like that to reduce the number of rows.
> CREATE INDEX idx_clt_rec_captured_at ON clt_rec USING btree ( captured_at );
So where does the actual runtime come from in the above plan? Is it
simply comparing lots of timestamps and that takes how long it takes?
Or is it calculating with those timestamps already?
Thanks!
[1]:
https://stackoverflow.com/questions/15977741/postgres-is-ignoring-a-timestamp-index-why
Mit freundlichen Grüßen,
Thorsten Schöning
--
Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/
Telefon...05151- 9468- 55
Fax...05151- 9468- 88
Mobil..0178-8 9468- 04
AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
