Re: quick question abt pg_dump and restore

On Jan 9, 2008 10:27 AM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> Josh Harrison escribió:
>
> > Another quick question...When you issue a query like this
> > select * from dummy limit 10
> > What 10 rows are fetched? like first 10 or last 10 or the first 10
> from
> > first block or.... ?
>
> Any 10. (First 10 in the physical table _if_ a seqscan is used).
>

Okay. Here is another scenario where Im confused.
I have a a table with around 30,000,000 recs. This is not a production
system but a test system. So in the test system generally we upload the rows
in some order say rows corresponding to a particular patient or something
like that. But in the production system, it generally doesn't happen like
that. The rows of 1 particular patient can be shuffled anywhere (ie.,
inserted in any order). We r trying to duplicate the same by shuffling te
data in the table so that the rows are not in any order and also not stored
in contiguous blocks

So now I have a table Dummy with 30,000,000 recs and a table Shuffled_Dummy
(Create table Shuffled_Dummy as select * from Dummy order by random() ) with
the same shuffled rows of dummy.

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)
----- the output results from dummy are different in the 2 databases
But
select * from shuffled_dummy limit 1000 (planner chooses seq scan)
----- the outputs from shuffled_dummy are same from both the database

Why?

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?

Thanks
josh