Thread: Seq scan of table?

Seq scan of table?

From
"Bjorn T Johansen"
Date:
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."
-----------------------------------------------------------------------------------------------


RE : Seq scan of table?

From
"Bruno BAGUETTE"
Date:
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



Re: Seq scan of table?

From
Tom Lane
Date:
"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

Re: RE : Seq scan of table?

From
"Bjorn T Johansen"
Date:
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
>
>
>



Re: Seq scan of table?

From
"Bjorn T Johansen"
Date:

> "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



Re: Seq scan of table?

From
"Bjorn T Johansen"
Date:
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)
>



Re: [PERFORM] Seq scan of table?

From
Richard Huxton
Date:
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

Re: [PERFORM] Seq scan of table?

From
Bjørn T Johansen
Date:
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




Re: Seq scan of table?

From
Jonathan Bartlett
Date:
> 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
>


Re: [PERFORM] Seq scan of table?

From
Ron Johnson
Date:
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


Re: [PERFORM] Seq scan of table?

From
Richard Huxton
Date:
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

Re: [PERFORM] Seq scan of table?

From
Tom Lane
Date:
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

Re: [PERFORM] Seq scan of table?

From
Neil Conway
Date:
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