> 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