On Thu, May 11, 2006 at 08:57:48AM -0400, Ketema Harris wrote: > Nested Loops on: > Nested Loop (cost=3.33..11.37 rows=1 width=268) (actual time=2.166..2.982 > > Nested Loops off: > Hash Join (cost=8.27..11.78 rows=1 width=268) (actual time=1.701..1.765 > > With nested loops enabled does it choose to use them because it sees the > estimated start up cost with loops as less? Does it not know that the total > query would be faster with the Hash Joins? This query is in development
Yes it does know; re-read the output. I believe the cases where the planner will look at startup cost over total cost are pretty limited; when LIMIT is used and I think sometimes when a CURSOR is used. > Statistics collecting and auto vacuum is enabled btw. I have an erd diagram > showing the table structures if anyone is interested in looking at it, just > let me know. Note that it's not terribly uncommon for the default stats target to be woefully inadequate for large sets of data, not that 100 rows a day is large. But it probably wouldn't hurt to bump the defaulst stats target up to 30 or 50 anyway. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
