Tom writes:
>> And when I run the suggested query, I get:
>
>> efamroot@kat efam=# select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630 where chunk_id = 1698936148 order by 1,2;
>> chunk_id | chunk_seq | ctid | xmin | xmax | length
>> ------------+-----------+--------------+------------+------+--------
>> 1698936148 | 0 | (52888694,2) | 1511390221 | 0 | 1996
>> 1698936148 | 1 | (52888694,4) | 1511390221 | 0 | 1148
>> (2 rows)
> Hmph. So if you EXPLAIN that query, does it show it's doing it as an
> indexscan? I'd expect so, but it's always good to make sure.
It does:
efamroot@kat efam=# SELECT * FROM efam.sendreference WHERE id = '189909908';
ERROR: unexpected chunk number 0 (expected 1) for toast value 1698936148 in pg_toast_10919630
efamroot@kat efam=# select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630 where chunk_id = 1698936148 order by 1,2;
chunk_id | chunk_seq | ctid | xmin | xmax | length
------------+-----------+--------------+------------+------+--------
1698936148 | 0 | (52888694,2) | 1511390221 | 0 | 1996
1698936148 | 1 | (52888694,4) | 1511390221 | 0 | 1148
(2 rows)
efamroot@kat efam=# explain select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630 where chunk_id = 1698936148 order by 1,2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Index Scan using pg_toast_10919630_index on pg_toast_10919630 (cost=0.57..2627179.25 rows=2135674 width=54)
Index Cond: (chunk_id = 1698936148::oid)
(2 rows)
> Assuming it does say that, then the other test I had in mind would
> involve "set enable_indexscan = 0", then repeat the EXPLAIN to make
> sure that you now get a seqscan plan (you might need to turn off
> enable_bitmapscan too), then do the query again and see whether the
> results are the same.
Ok (if I don't disable bitmapscan, I get Bitmap Heap Scans in the EXPLAIN, so):
efamroot@kat efam=# set enable_indexscan = 0;
SET
efamroot@kat efam=# set enable_bitmapscan = 0;
SET
efamroot@kat efam=# explain select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630 where chunk_id = 1698936148 order by 1,2;
QUERY PLAN
-------------------------------------------------------------------------------------
Sort (cost=96465280.57..96470619.75 rows=2135674 width=54)
Sort Key: chunk_seq
-> Seq Scan on pg_toast_10919630 (cost=0.00..96240754.39 rows=2135674 width=54)
Filter: (chunk_id = 1698936148::oid)
(4 rows)
efamroot@kat efam=# select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data) from pg_toast.pg_toast_10919630 where chunk_id = 1698936148 order by 1,2;
[... still waiting for the result, I will return with what it said
when the server does ...]
Best regards,
Adam
--
"No more than that, but very powerful all the Adam Sjøgren
same; simple things are good." adsj@xxxxxxxxxxxxx
