Hi ,
We have a set of partitioned tables and we run the query on main table
the query is
select uniq_id ,profile_id from general.profile_log where
profile_id=3528336 order by uniq_id desc limit 5;
there is a index on profile_id on the child tables of profile_log. The
query on profile_id does not chooses that
index instead it uses an index on uniq_id ( the sorting column).
Since the number of child table is huge i am only posting an excerpt
of the plan .
----------------------------------------------------------------
Filter: (profile_id = 3528336)
-> Index Scan Backward using profile_log_2011_08_pkey
on profile_log_2011_08 profile_log (cost=0.00..15815.11 rows=3
width=8)
Filter: (profile_id = 3528336)
-> Index Scan Backward using profile_log_2011_09_pkey
on profile_log_2011_09 profile_log (cost=0.00..17851.91 rows=76
width=8)
---------------------------------------------------------------------
the index profile_log_2011_09_pkey is the index on the column uniq_id
the query is *very slow* , 39 seconds
how ever if we just change limit 5 to limit 15 then the plan changes
Index Cond: (profile_id = 3528336)
-> Bitmap Heap Scan on profile_log_2011_08
profile_log (cost=4.31..16.13 rows=3 width=8)
Recheck Cond: (profile_id = 3528336)
-> Bitmap Index Scan on
profile_log_2011_08_profile_id (cost=0.00..4.31 rows=3 width=0)
Index Cond: (profile_id = 3528336)
-> Bitmap Heap Scan on profile_log_2011_09
profile_log (cost=4.89..285.93 rows=76 width=8)
Recheck Cond: (profile_id = 3528336)
-> Bitmap Index Scan on
profile_log_2011_09_profile_id (cost=0.00..4.87 rows=76 width=0)
Index Cond: (profile_id = 3528336)
the index on profile_id is being used and the query is very fast ( 50 ms)
I am using the most recent released version of postgresql at this
moment which is 9.1.1
can any one please suggest , I think autovaccum is on.
regds
mallah
