To: "Greg Stark" <gsstark@mit.edu>, "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad plan on a view
References: <op.s5qo4bx8cigqcu@apollo13> <10652.1141226168@sss.pgh.pa.us>
	<87r75mup5s.fsf@stark.xeocode.com>
Message-ID: <op.s5qucsb7cigqcu@apollo13>
Date: Wed, 01 Mar 2006 17:49:30 +0100
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <87r75mup5s.fsf@stark.xeocode.com>
User-Agent: Opera M2/8.51 (Linux, build 1462)


> 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