On Jan 9, 2008 10:27 AM, Alvaro Herrera <
alvherre@commandprompt.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