Re: Bad plan on a view

> 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