Message-ID: <4DBEAF7B.1060409@yagni.com>
Date: Mon, 02 May 2011 06:19:55 -0700
From: Wayne Conrad <wconrad@yagni.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.15) Gecko/20101030 Icedove/3.0.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: 8.4.7, incorrect estimate
References: <4DBB024B.5050500@databill.com>
	<4DBAC75C020000250003D06B@gw.wicourts.gov>
In-Reply-To: <4DBAC75C020000250003D06B@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Replying to the list this time (oops)...

On 04/29/11 12:33, Kevin Grittner wrote:
> Also, make sure that you run ANALYZE against your temp table right
> before running your query.

I did that, and also added an index to it.  That had no effect on the 
run time, but did fix the estimate for the temporary table.

On 04/29/11 12:12, Kevin Grittner wrote:
> Out of curiosity, what do you get with?:
>
> explain analyze
> select
>      page_number,
>      ps_id,
>      ps_page_id
>    from ps_page p
>    where exists
>          (
>            select * from documents_ps_page d
>              where d.ps_page_id = p.ps_page_id
>                and exists
>                    (select * from temp_document_ids t
>                       where t.document_id = d.document_id)
>          )
>    order by ps_page_id

  Merge Semi Join  (cost=186501.69..107938082.91 rows=29952777 width=12) 
(actual time=242801.828..244572.318 rows=5 loops=1)
    Merge Cond: (p.ps_page_id = d.ps_page_id)
    ->  Index Scan using ps_page_pkey on ps_page p 
(cost=0.00..2995637.47 rows=86141904 width=12) (actual 
time=0.052..64140.510 rows=85401688 loops=1)
    ->  Index Scan using documents_ps_page_ps_page_id_idx on 
documents_ps_page d  (cost=0.00..104384546.06 rows=37358320 width=4) 
(actual time=161483.657..163254.131 rows=5 loops=1)
          Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
          SubPlan 1
            ->  Seq Scan on temp_doc_ids t  (cost=0.00..1.35 rows=1 
width=0) (never executed)
                  Filter: (document_id = $0)
          SubPlan 2
            ->  Seq Scan on temp_doc_ids t  (cost=0.00..1.34 rows=5 
width=35) (actual time=0.005..0.007 rows=5 loops=1)
  Total runtime: 244572.432 ms
(11 rows)