Seq scan of table?

[Bjorn T Johansen]

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
bt*@havleik.no Havleik Consulting
Phone : [phone] Conradisvei 4
Fax : [phone] N-1338 Sandvika
Cellular : [phone] 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 ma*******@postgresql.org)

[Bruno BAGUETTE]

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 - pg******@baguette.net

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

"Bjorn T Johansen" <bt*@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bjorn T Johansen]

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 - pg******@baguette.net

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bjorn T Johansen]

"Bjorn T Johansen" <bt*@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Neil Conway <ne***@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...1/msg00468.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster