Re: Problem with planner - Mailing list pgsql-general

From Tom Lane
Subject Re: Problem with planner
Date
Msg-id 19586.1312906308@sss.pgh.pa.us
Whole thread Raw
In response to Re: Problem with planner  (hubert depesz lubaczewski <depesz@depesz.com>)
Responses Re: Problem with planner  (hubert depesz lubaczewski <depesz@depesz.com>)
Re: Problem with planner  (hubert depesz lubaczewski <depesz@depesz.com>)
List pgsql-general
hubert depesz lubaczewski <depesz@depesz.com> writes:
> as for good plan. sure. If i'll disable bitmap scans, I get:

> $ explain analyze select count(*) from objects where state='active' and ending_tsz <= (select now() - '1
day'::interval); 
>                                                                        QUERY PLAN
                                   
>
---------------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=7651119.35..7651119.36 rows=1 width=0) (actual time=63.150..63.151 rows=1 loops=1)
>    InitPlan
>      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
>    ->  Index Scan using objects_ending_tsz_active on objects  (cost=0.00..7642758.99 rows=3344138 width=0) (actual
time=63.131..63.131rows=0 loops=1) 
>          Index Cond: (ending_tsz <= $0)
>  Total runtime: 63.279 ms
> (6 rows)

There's absolutely no hope of getting the planner to make intelligent
plan choices when its estimates are that far off (3344138 rows estimated
versus none actual).  It's just luck if you get the "right" plan despite
that.

Now part of the reason why the estimate is so bad is the sub-select,
which completely defeats selectivity estimation for the ending_tsz
condition.  Could we see EXPLAIN ANALYZE for the query without a
sub-select, both with and without enable_bitmapscan?

            regards, tom lane

pgsql-general by date:

Previous
From: Paul M Foster
Date:
Subject: Re: Update with ORDER BY and LIMIT
Next
From: Tom Lane
Date:
Subject: Re: Update with ORDER BY and LIMIT