Re: Problem with planner

On Tue, Aug 09, 2011 at 05:18:38PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz(at)depesz(dot)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.207 rows=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) (actual time=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/