Re: [PERFORM] Odd explain estimate
On Fri, Aug 01, 2003 at 08:16:12AM -0400, Andrew Sullivan wrote: > On Thu, Jul 31, 2003 at 05:59:59PM -0500, Jim C. Nasby wrote: > > > > Well, if I don't do this it wants to seqscan a table that occupies 350k > > pages, instead of pulling a couple thousand rows. I started running it > > with the seqscan and it's already taken way longer than it does if I > > disable seqscan. > > That was indeed the question. > > If it uses a seqscan when it ought not to do, then there's something > wrong with the statistics, or you haven't vacuum analysed correctly, > or your table needs vacuum full (is it really 350k pages, or is that > mostly dead space?), &c. -- all the usual bad-seqscan candidates. > > enable_seqscan=off is probably not a good strategy for any moderately > complicated query. If the planner were perfect, of course, you'd > never need it at all. Set statistics on the ID colum to 1000, vacuum analyze, and it's good to go now. Thanks for your help! -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Odd explain estimate
On Thu, Jul 31, 2003 at 05:59:59PM -0500, Jim C. Nasby wrote: > > Well, if I don't do this it wants to seqscan a table that occupies 350k > pages, instead of pulling a couple thousand rows. I started running it > with the seqscan and it's already taken way longer than it does if I > disable seqscan. That was indeed the question. If it uses a seqscan when it ought not to do, then there's something wrong with the statistics, or you haven't vacuum analysed correctly, or your table needs vacuum full (is it really 350k pages, or is that mostly dead space?), &c. -- all the usual bad-seqscan candidates. enable_seqscan=off is probably not a good strategy for any moderately complicated query. If the planner were perfect, of course, you'd never need it at all. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd explain estimate
On Thu, Jul 31, 2003 at 04:59:21PM -0400, Andrew Sullivan wrote: > On Thu, Jul 31, 2003 at 02:51:45PM -0500, Jim C. Nasby wrote: > If you really needed to set enable_seqscan=false (did you really? > Are you sure that's not the cheapest way?), you might want to > investigate expainding the statistics on the indexed column, > increasing the correlation through clustering, and other such tricks. Well, if I don't do this it wants to seqscan a table that occupies 350k pages, instead of pulling a couple thousand rows. I started running it with the seqscan and it's already taken way longer than it does if I disable seqscan. I guess I'll try expanding the statistics. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Odd explain estimate
On Thu, Jul 31, 2003 at 02:51:45PM -0500, Jim C. Nasby wrote: > Why is pgsql estimating a cost of 1 for retire_today in this > query? I analyzed it, and there's nothing very odd about it, other than > it's a temp table. > > BTW, I had to set enable_seqscan=false to get this, otherwise it wants That's why. When you do that, it just automatically adds 1 to the cost of a seqscan. It can't really disable it, because there might be no other way to pull the result. If you really needed to set enable_seqscan=false (did you really? Are you sure that's not the cheapest way?), you might want to investigate expainding the statistics on the indexed column, increasing the correlation through clustering, and other such tricks. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
