Re: quick question abt pg_dump and restore

Поиск
Список
Период
Сортировка
От Josh Harrison
Тема Re: quick question abt pg_dump and restore
Дата
Msg-id 8d89ea1d0801090919u6b89b255q208a84aa227da3f6@mail.gmail.com
обсуждение исходный текст
Ответ на Re: quick question abt pg_dump and restore  (Andreas Kretschmer <akretschmer@spamfence.net>)
Ответы Re: quick question abt pg_dump and restore  (Gregory Stark <stark@enterprisedb.com>)
Список pgsql-general
Дерево обсуждения


On Jan 9, 2008 11:56 AM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
Josh Harrison <joshques@gmail.com> 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: count(*) and bad design was: Experiences with extensibility
Следующее
От: "Albe Laurenz"
Дата:
Сообщение: Re: Insert waiting for update?