From: PFC <lists@peufeu.com>
To: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad plan on a view
Date: Wed, 01 Mar 2006 17:49:30 +0100
Message-ID: <op.s5qucsb7cigqcu@apollo13> (raw)
In-Reply-To: <87r75mup5s.fsf@stark.xeocode.com>
References: <op.s5qo4bx8cigqcu@apollo13>
<10652.1141226168@sss.pgh.pa.us>
<87r75mup5s.fsf@stark.xeocode.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, gsstark@mit.edu, tgl@sss.pgh.pa.us
Subject: Re: Bad plan on a view
In-Reply-To: <op.s5qucsb7cigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox