Re: quick question abt pg_dump and restore

["Josh Harrison" <joshques@xxxxxxxxx>]

On Jan 9, 2008 11:56 AM, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote:

Josh Harrison <joshques@xxxxxxxxx> schrieb:

> My questions
> 1.  I pg_dumped dummy and Shuffled_dummy (from database1) to another database
> (database2)
> When I issued the query in both database (database1 and database2)
>
> select * from dummy limit 1000 ( the planner chooses seq scan for this query)

> select * from shuffled_dummy limit 1000 (planner chooses seq scan)
>
>

> 2. Also when does the planner switch from choosing index scan to bitmap index
> scan? Is it dependent on the number of rows to be retrieved or the position of
> the relevant data in the blocks or something else?

For a select * ... without a WHERE the db can't use an index, this query
forced a seq-scan.

A index is used when:
- a index are created
- a propper WHERE or ORDER BY in the query
- this index is useful
 (a index isn't useful, for instance, for a small table or when almost
 all rows are in the result set)

A bitmap index scan performed when:
- 2 or more propper indexes available
- see above

Thanks
In my database, I have a table 'person' containing roughly 30,000,000 records

explain select count(*) from person where person_id >  1147000000
QUERY PLAN                                                                                                   
 ------------------------------------------------------------------------------------------------------------ 
 Aggregate  (cost=307708.20..307708.21 rows=1 width=0)                                                        
   ->  Index Scan using person_pk on person  (cost=0.00..307379.79 rows=131364 width=0) 
         Index Cond: (person_id > 1147000000::numeric)                                             

 3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 
This returns the result
count    
 -------- 
 78718  

But for this query where the condition is slightly different the query plan is different. The planner goes for bitmap index

explain select count(*) from person where person_id >  1146000000
QUERY PLAN                                                                                      
 ----------------------------------------------------------------------------------------------- 
 Aggregate  (cost=342178.51..342178.52 rows=1 width=0)                                           
   ->  Bitmap Heap Scan on person  (cost=
3120.72..341806.71 rows=148721 width=0)      
         Recheck Cond: (person_id > 1146000000::numeric)                              
         ->  Bitmap Index Scan on person_pk  (cost=0.00..3083.53 rows=148721 width=0) 
               Index Cond: (person_id > 1146000000::numeric)                          

 5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 
and the result is
count    
 -------- 
 90625  

How does the planner choose the plan? 
josh