Re: Problem with planner - Mailing list pgsql-general

On Tue, Aug 09, 2011 at 05:18:38PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > I solved the problem by adding "enable_bitmapscan = false" (and keeping
> > the query in original format, with subselect) which caused the plan to
> > be ok.
>
> I doubt that solution is any more robust than what you had before ---
> in particular, it's likely to fall back to seqscans.
>
> > but I'm much more interested to understand why pg chooses *not* to use
> > index which is tailored specifically for the query - it has exactly
> > matching where clause, and it indexes the column that we use for
> > comparison.
>
> Because the planner thinks it will have to pull a huge number of rows
> from the index.  Whether the index is "tailored" for the query
> is irrelevant if it looks more expensive to use than a seqscan.


The query with enabled bitmap scans finally finished:
                                                                                   QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=9117479.68..9117479.69 rows=1 width=0) (actual time=24964197.316..24964197.317 rows=1 loops=1)
   ->  Bitmap Heap Scan on objects  (cost=326375.14..9097597.32 rows=7952942 width=0) (actual
time=24804152.598..24964197.207rows=48 loops=1) 
         Recheck Cond: (state = 'active'::text)
         Filter: (ending_tsz <= (now() - '1 day'::interval))
         ->  Bitmap Index Scan on objects_stat_user_id_creation_tsz  (cost=0.00..324386.90 rows=9754574 width=0)
(actualtime=24788705.254..24788705.254 rows=10582798 loops=1) 
               Index Cond: (state = 'active'::text)
 Total runtime: 24964211.224 ms
(7 rows)

In the mean time, based on your other mail, I created this index:

create index concurrently depesz_test_idx on objects (state, ending_tsz) WHERE state = 'active'::text;

and now it's being used by plans:
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8510568.24..8510568.25 rows=1 width=0)
   ->  Bitmap Heap Scan on objects  (cost=183260.11..8490232.49 rows=8134302 width=0)
         Recheck Cond: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 day'::interval)))
         ->  Bitmap Index Scan on depesz_test_idx  (cost=0.00..181226.54 rows=8134302 width=0)
               Index Cond: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 day'::interval)))
(5 rows)

but when I'll disable bitmap scans it reverts back to seq scan.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/