pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Seq scan of table?
11+ messages / 7 participants
[nested] [flat]

* Seq scan of table?
@ 2003-09-05 06:08 Bjorn T Johansen <btj@havleik.no>
  2003-09-05 06:44 ` Re: Seq scan of table? Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  0 siblings, 2 replies; 11+ messages in thread

From: Bjorn T Johansen @ 2003-09-05 06:08 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
@ 2003-09-05 06:44 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-05 06:57   ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  1 sibling, 1 reply; 11+ messages in thread

From: Tom Lane @ 2003-09-05 06:44 UTC (permalink / raw)
  To: Bjorn T Johansen <btj@havleik.no>; +Cc: pgsql-general@postgresql.org

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 06:44 ` Re: Seq scan of table? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-05 06:57   ` Bjorn T Johansen <btj@havleik.no>
  0 siblings, 0 replies; 11+ messages in thread

From: Bjorn T Johansen @ 2003-09-05 06:57 UTC (permalink / raw)
  To: pgsql-general@postgresql.org



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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
@ 2003-09-05 08:47 ` Bjorn T Johansen <btj@havleik.no>
  2003-09-05 10:07   ` Re: [PERFORM] Seq scan of table? Richard Huxton <dev@archonet.com>
  2003-09-05 14:39   ` Re: Seq scan of table? Jonathan Bartlett <johnnyb@eskimo.com>
  1 sibling, 2 replies; 11+ messages in thread

From: Bjorn T Johansen @ 2003-09-05 08:47 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; +Cc: pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PERFORM] Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
@ 2003-09-05 10:07   ` Richard Huxton <dev@archonet.com>
  2003-09-05 13:23     ` Re: [PERFORM] Seq scan of table? Bjørn T Johansen <btj@havleik.no>
  2003-09-05 18:20     ` Re: [PERFORM] Seq scan of table? Neil Conway <neilc@samurai.com>
  1 sibling, 2 replies; 11+ messages in thread

From: Richard Huxton @ 2003-09-05 10:07 UTC (permalink / raw)
  To: Bjorn T Johansen <btj@havleik.no>; pgsql-general@postgresql.org; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PERFORM] Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 10:07   ` Re: [PERFORM] Seq scan of table? Richard Huxton <dev@archonet.com>
@ 2003-09-05 13:23     ` Bjørn T Johansen <btj@havleik.no>
  1 sibling, 0 replies; 11+ messages in thread

From: Bjørn T Johansen @ 2003-09-05 13:23 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-performance

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






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PERFORM] Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 10:07   ` Re: [PERFORM] Seq scan of table? Richard Huxton <dev@archonet.com>
@ 2003-09-05 18:20     ` Neil Conway <neilc@samurai.com>
  2003-09-05 19:37       ` Re: [PERFORM] Seq scan of table? Richard Huxton <dev@archonet.com>
  2003-09-05 21:52       ` Re: [PERFORM] Seq scan of table? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 11+ messages in thread

From: Neil Conway @ 2003-09-05 18:20 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: Bjorn T Johansen <btj@havleik.no>; PostgreSQL General <pgsql-general@postgresql.org>; pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PERFORM] Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 10:07   ` Re: [PERFORM] Seq scan of table? Richard Huxton <dev@archonet.com>
  2003-09-05 18:20     ` Re: [PERFORM] Seq scan of table? Neil Conway <neilc@samurai.com>
@ 2003-09-05 19:37       ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Richard Huxton @ 2003-09-05 19:37 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: Bjorn T Johansen <btj@havleik.no>; PostgreSQL General <pgsql-general@postgresql.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PERFORM] Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 10:07   ` Re: [PERFORM] Seq scan of table? Richard Huxton <dev@archonet.com>
  2003-09-05 18:20     ` Re: [PERFORM] Seq scan of table? Neil Conway <neilc@samurai.com>
@ 2003-09-05 21:52       ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 11+ messages in thread

From: Tom Lane @ 2003-09-05 21:52 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: Richard Huxton <dev@archonet.com>; Bjorn T Johansen <btj@havleik.no>; PostgreSQL General <pgsql-general@postgresql.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
@ 2003-09-05 14:39   ` Jonathan Bartlett <johnnyb@eskimo.com>
  2003-09-05 16:09     ` Re: [PERFORM] Seq scan of table? Ron Johnson <ron.l.johnson@cox.net>
  1 sibling, 1 reply; 11+ messages in thread

From: Jonathan Bartlett @ 2003-09-05 14:39 UTC (permalink / raw)
  To: Bjorn T Johansen <btj@havleik.no>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [PERFORM] Seq scan of table?
  2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 08:47 ` Re: Seq scan of table? Bjorn T Johansen <btj@havleik.no>
  2003-09-05 14:39   ` Re: Seq scan of table? Jonathan Bartlett <johnnyb@eskimo.com>
@ 2003-09-05 16:09     ` Ron Johnson <ron.l.johnson@cox.net>
  0 siblings, 0 replies; 11+ messages in thread

From: Ron Johnson @ 2003-09-05 16:09 UTC (permalink / raw)
  To: PgSQL General ML <pgsql-general@postgresql.org>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2003-09-05 21:52 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-05 06:08 Seq scan of table? Bjorn T Johansen <btj@havleik.no>
2003-09-05 06:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-05 06:57   ` Bjorn T Johansen <btj@havleik.no>
2003-09-05 08:47 ` Bjorn T Johansen <btj@havleik.no>
2003-09-05 10:07   ` Richard Huxton <dev@archonet.com>
2003-09-05 13:23     ` Bjørn T Johansen <btj@havleik.no>
2003-09-05 18:20     ` Neil Conway <neilc@samurai.com>
2003-09-05 19:37       ` Richard Huxton <dev@archonet.com>
2003-09-05 21:52       ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-05 14:39   ` Jonathan Bartlett <johnnyb@eskimo.com>
2003-09-05 16:09     ` Ron Johnson <ron.l.johnson@cox.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox