While TOAST has a similar goal I don't think it has enough AI to
completely
replace this manual process. It suffers in a number of use cases:
1) When you have a large number of moderate sized text fields instead of
a single very large text field. This is probably the case here.
Exactly.
2) When you know exactly which fields you'll be searching on and which
you won't be. Often many speed-sensitive queries don't need to access the
extended information at all.
Also true. I only need the large fields to display the few rows which
survive the LIMIT...
Here's one of the same :
Although the subselect has no influence on the WHERE condition, 97021
subselects are computed, and only 10 kept...
This data also bloats the sort (if the subselect yields a large text
field instead of an int, the sort time doubles).
explain analyze select raw_annonce_id, price, rooms, surface, terrain,
contact_telephones, description, (SELECT price FROM raw_annonces r WHERE
r.id=raw_annonce_id) from annonces where price is not null order by price
desc limit 10;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=459568.37..459568.40 rows=10 width=272) (actual
time=1967.360..1967.368 rows=10 loops=1)
-> Sort (cost=459568.37..459812.60 rows=97689 width=272) (actual
time=1967.357..1967.361 rows=10 loops=1)
Sort Key: price
-> Seq Scan on annonces (cost=0.00..443102.59 rows=97689
width=272) (actual time=0.059..949.507 rows=97021 loops=1)
Filter: (price IS NOT NULL)
SubPlan
-> Index Scan using raw_annonces_pkey on raw_annonces r
(cost=0.00..4.46 rows=1 width=8) (actual time=0.005..0.006 rows=1
loops=97021)
Index Cond: (id = $0)
Total runtime: 1988.786 ms
