Thread: Re: [GENERAL] Seq scan of table?
I think I have found out why.. I have a where clause on a ID field but it seems like I need to cast this integer to the same integer as the field is defined in the table, else it will do a tablescan. Is this assumtion correct? And if it is, do I then need to change all my sql's to cast the where clause where I just have a number (eg where field = 1) to force the planner to use index scan instead of seq scan? BTJ > I am trying to tune my database and I discovered one select that does a > seq scan on a table but I can't see why... All the join fields are indexed > and I am returning just one record, so no sort is done. > Does it just pick seq scan for the heck of it or is it a reason? > > Regards, > > BTJ > > ----------------------------------------------------------------------------------------------- > Bjørn T Johansen (BSc,MNIF) > Executive Manager > btj@havleik.no Havleik Consulting > Phone : +47 67 54 15 17 Conradisvei 4 > Fax : +47 67 54 13 91 N-1338 Sandvika > Cellular : +47 926 93 298 http://www.havleik.no > ----------------------------------------------------------------------------------------------- > "The stickers on the side of the box said "Supported Platforms: Windows > 98, Windows NT 4.0, > Windows 2000 or better", so clearly Linux was a supported platform." > ----------------------------------------------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Friday 05 September 2003 09:47, Bjorn T Johansen wrote: > I think I have found out why.. I have a where clause on a ID field but it > seems like I need to cast this integer to the same integer as the field is > defined in the table, else it will do a tablescan. > > Is this assumtion correct? And if it is, do I then need to change all my > sql's to cast the where clause where I just have a number (eg where field > = 1) to force the planner to use index scan instead of seq scan? PG's parser will assume an explicit number is an int4 - if you need an int8 etc you'll need to cast it, yes. You should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. -- Richard Huxton Archonet Ltd
On Fri, 2003-09-05 at 12:07, Richard Huxton wrote: > On Friday 05 September 2003 09:47, Bjorn T Johansen wrote: > > I think I have found out why.. I have a where clause on a ID field but it > > seems like I need to cast this integer to the same integer as the field is > > defined in the table, else it will do a tablescan. > > > > Is this assumtion correct? And if it is, do I then need to change all my > > sql's to cast the where clause where I just have a number (eg where field > > = 1) to force the planner to use index scan instead of seq scan? > > PG's parser will assume an explicit number is an int4 - if you need an int8 > etc you'll need to cast it, yes. > You should find plenty of discussion of why in the archives, but the short > reason is that PG's type structure is quite flexible which means it can't > afford to make too many assumptions. Oki, I am using both int2 and int8 as well, so that explains it... Thanks! BTJ
> I think I have found out why.. I have a where clause on a ID field but it > seems like I need to cast this integer to the same integer as the field is > defined in the table, else it will do a tablescan. Yes, this is correct > Is this assumtion correct? And if it is, do I then need to change all my > sql's to cast the where clause where I just have a number (eg where field > = 1) to force the planner to use index scan instead of seq scan? Someone correct me if I'm wrong, but I believe numbers are int4's, so they need to be cast if your column is not an int4. Jon > > > BTJ > > > I am trying to tune my database and I discovered one select that does a > > seq scan on a table but I can't see why... All the join fields are indexed > > and I am returning just one record, so no sort is done. > > Does it just pick seq scan for the heck of it or is it a reason? > > > > Regards, > > > > BTJ > > > > ----------------------------------------------------------------------------------------------- > > Bj�rn T Johansen (BSc,MNIF) > > Executive Manager > > btj@havleik.no Havleik Consulting > > Phone : +47 67 54 15 17 Conradisvei 4 > > Fax : +47 67 54 13 91 N-1338 Sandvika > > Cellular : +47 926 93 298 http://www.havleik.no > > ----------------------------------------------------------------------------------------------- > > "The stickers on the side of the box said "Supported Platforms: Windows > > 98, Windows NT 4.0, > > Windows 2000 or better", so clearly Linux was a supported platform." > > ----------------------------------------------------------------------------------------------- > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
On Fri, 2003-09-05 at 09:39, Jonathan Bartlett wrote: > > I think I have found out why.. I have a where clause on a ID field but it > > seems like I need to cast this integer to the same integer as the field is > > defined in the table, else it will do a tablescan. > > Yes, this is correct > > > Is this assumtion correct? And if it is, do I then need to change all my > > sql's to cast the where clause where I just have a number (eg where field > > = 1) to force the planner to use index scan instead of seq scan? > > Someone correct me if I'm wrong, but I believe numbers are int4's, so they > need to be cast if your column is not an int4. You mean "constant" scalars? Yes, constants scalars are interpreted as int4. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "Millions of Chinese speak Chinese, and it's not hereditary..." Dr. Dean Edell
On Friday 05 September 2003 19:20, Neil Conway wrote: > On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: > > PG's parser will assume an explicit number is an int4 - if you need an > > int8 etc you'll need to cast it, yes. > > Or enclose the integer literal in single quotes. > > > You should find plenty of discussion of why in the archives, but the > > short reason is that PG's type structure is quite flexible which means it > > can't afford to make too many assumptions. > > Well, it's definitely a bug in PG, it's "quite flexible" type structure > notwithstanding. It certainly catches out a lot of people. I'd guess it's in the top three issues in the general/sql lists. I'd guess part of the problem is it's so silent. In some ways it would be better to issue a NOTICE every time a typecast is forced in a comparison - irritating as that would be. -- Richard Huxton Archonet Ltd
On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: > PG's parser will assume an explicit number is an int4 - if you need an int8 > etc you'll need to cast it, yes. Or enclose the integer literal in single quotes. > You should find plenty of discussion of why in the archives, but the short > reason is that PG's type structure is quite flexible which means it can't > afford to make too many assumptions. Well, it's definitely a bug in PG, it's "quite flexible" type structure notwithstanding. -Neil
Neil Conway <neilc@samurai.com> writes: > On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: >> You should find plenty of discussion of why in the archives, but the short >> reason is that PG's type structure is quite flexible which means it can't >> afford to make too many assumptions. > Well, it's definitely a bug in PG, it's "quite flexible" type structure > notwithstanding. Let's say it's something we'd really like to fix ;-) ... and will, as soon as we can figure out a cure that's not worse than the disease. Dorking around with the semantics of numeric expressions has proven to be a risky business. See, eg, the thread starting here: http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php regards, tom lane