Re: quick question abt pg_dump and restore

On Jan 9, 2008 11:56 AM, Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
wrote:

> Josh Harrison <joshques(at)gmail(dot)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.79rows=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