Thread: Seq scan of table?
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." -----------------------------------------------------------------------------------------------
Hello, > 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? Are the join fields both of the exactly same type ? If no (eg : INT2 and INT4) you must cast in order to have the same type. If the join fields are not of the same type, PostgreSQL will do a seq scan. I had exactly the same problem and learned here that tip :-) Hope this help, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
"Bjorn T Johansen" <btj@havleik.no> writes:
> 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?
Who's to say, when you gave us no details? Show us the table schemas,
the exact query, and EXPLAIN ANALYZE output, and you might get useful
responses.
(btw, pgsql-performance would be a more appropriate list for this issue
than pgsql-general.)
regards, tom lane
Well, I just checked and all the join fields are of the same type... BTJ > Hello, > >> 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? > > Are the join fields both of the exactly same type ? If no (eg : INT2 and > INT4) > you must cast in order to have the same type. > > If the join fields are not of the same type, PostgreSQL will do a seq > scan. > > I had exactly the same problem and learned here that tip :-) > > Hope this help, > > --------------------------------------- > Bruno BAGUETTE - pgsql-ml@baguette.net > > >
> "Bjorn T Johansen" <btj@havleik.no> writes: >> 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? > > Who's to say, when you gave us no details? Show us the table schemas, > the exact query, and EXPLAIN ANALYZE output, and you might get useful > responses. > > (btw, pgsql-performance would be a more appropriate list for this issue > than pgsql-general.) > > regards, tom lane > Well, since the select involves 10-12 tables and a large sql, I just thought I would try without all that information first... :) And yes, pgsql-performance sounds like the right list.... 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. 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
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
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