pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
why Sequencial Scan when selecting on primary key of table?
8+ messages / 4 participants
[nested] [flat]

* why Sequencial Scan when selecting on primary key of table?
@ 2003-05-31 16:30 Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:02 ` Re: why Sequencial Scan when selecting on primary key of table? Andrew Sullivan <andrew@libertyrms.info>
  2003-05-31 17:13 ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 8+ messages in thread

From: Brian Tarbox @ 2003-05-31 16:30 UTC (permalink / raw)
  To: pgsql-performance

I have a simple table with a dozen integer fields and a primary key.

When I say "explain select * from Patient where Patient_primary_key = 100"

I get sequential scan.

I've just converted my application from MySQL and am seeing everything run
about 3X slower.  What do I have to do to get postgres to use indexes?

Brian Tarbox




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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
@ 2003-05-31 17:02 ` Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 0 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-05-31 17:02 UTC (permalink / raw)
  To: pgsql-performance

On Sat, May 31, 2003 at 12:30:40PM -0400, Brian Tarbox wrote:
> I have a simple table with a dozen integer fields and a primary key.
> 
> When I say "explain select * from Patient where Patient_primary_key = 100"
> 
> I get sequential scan.
> 
> I've just converted my application from MySQL and am seeing everything run
> about 3X slower.  What do I have to do to get postgres to use indexes?

Usual questions: have you vacuumed?  EXPLAIN ANALYSE output, schema,
&c.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
@ 2003-05-31 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-31 17:45   ` Re: why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:51   ` Re: why Sequencial Scan when selecting on primary key of table? Rod Taylor <rbt@rbt.ca>
  1 sibling, 2 replies; 8+ messages in thread

From: Tom Lane @ 2003-05-31 17:13 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: pgsql-performance

"Brian Tarbox" <btarbox@theworld.com> writes:
> When I say "explain select * from Patient where Patient_primary_key = 100"
> I get sequential scan.

Perhaps Patient_primary_key is not an integer field?  If not, you need
to cast the constant 100 to the right type.  Or write '100' with
single quotes around it, which leaves Postgres to choose the constant's
datatype.  (Yeah, I know, it's a pain in the neck.  We've had a lot of
discussions about how to fix this without breaking datatype extensibility;
no luck so far.)

			regards, tom lane



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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:13 ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-05-31 17:45   ` Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:55     ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-31 18:02     ` Re: why Sequencial Scan when selecting on primary key of table? Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 2 replies; 8+ messages in thread

From: Brian Tarbox @ 2003-05-31 17:45 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

The primary key field is an integer and I have performed vacuum analyse but
that does not seem to change anything.

I've also heard that postgres will not indexes when JOINing tables.  Can
that really be true??

Brian


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Saturday, May 31, 2003 1:14 PM
To: Brian Tarbox
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] why Sequencial Scan when selecting on primary key
of table?


"Brian Tarbox" <btarbox@theworld.com> writes:
> When I say "explain select * from Patient where Patient_primary_key = 100"
> I get sequential scan.

Perhaps Patient_primary_key is not an integer field?  If not, you need
to cast the constant 100 to the right type.  Or write '100' with
single quotes around it, which leaves Postgres to choose the constant's
datatype.  (Yeah, I know, it's a pain in the neck.  We've had a lot of
discussions about how to fix this without breaking datatype extensibility;
no luck so far.)

			regards, tom lane

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

http://archives.postgresql.org




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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:13 ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-31 17:45   ` Re: why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
@ 2003-05-31 17:55     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 8+ messages in thread

From: Tom Lane @ 2003-05-31 17:55 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: pgsql-performance

"Brian Tarbox" <btarbox@theworld.com> writes:
> The primary key field is an integer and I have performed vacuum analyse but
> that does not seem to change anything.

Hm.  So how big is the table, exactly?  On small tables a seqscan will
be preferred because the extra I/O to examine the index costs more than
the CPU to examine all the tuples on a disk page.

> I've also heard that postgres will not indexes when JOINing tables.  Can
> that really be true??

We have some join methods that like indexes and we have some that find
no benefit in 'em.  Again, testing on toy-size tables is not a good
guide to what will happen on larger tables.

			regards, tom lane



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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:13 ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-31 17:45   ` Re: why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
@ 2003-05-31 18:02     ` Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 0 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-05-31 18:02 UTC (permalink / raw)
  To: pgsql-performance

On Sat, May 31, 2003 at 01:45:50PM -0400, Brian Tarbox wrote:
> The primary key field is an integer and I have performed vacuum analyse but
> that does not seem to change anything.

int4?  int8?  int2?  Makes a difference.  Please post the results of
EXPLAIN ANALYSE on the query you're having trouble with, and someone
may be able to help you.  (You'll need to show us the table, too.)

> I've also heard that postgres will not indexes when JOINing tables.  Can
> that really be true??

No.  

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:13 ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-05-31 17:51   ` Rod Taylor <rbt@rbt.ca>
  2003-05-31 18:12     ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 8+ messages in thread

From: Rod Taylor @ 2003-05-31 17:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Brian Tarbox <btarbox@theworld.com>; pgsql-performance

On Sat, 2003-05-31 at 13:13, Tom Lane wrote:
> "Brian Tarbox" <btarbox@theworld.com> writes:
> > When I say "explain select * from Patient where Patient_primary_key = 100"
> > I get sequential scan.
> 
> Perhaps Patient_primary_key is not an integer field?  If not, you need
> to cast the constant 100 to the right type.  Or write '100' with
> single quotes around it, which leaves Postgres to choose the constant's
> datatype.

Out of curiosity, why don't we confirm the unquoted value is an integer,
numeric, etc, then change it into type 'unknown'?  From that point
forward it would be treated like it's quoted counterpart.

Is this noticeably slower or am I missing something?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

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

* Re: why Sequencial Scan when selecting on primary key of table?
  2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
  2003-05-31 17:13 ` Re: why Sequencial Scan when selecting on primary key of table? Tom Lane <tgl@sss.pgh.pa.us>
  2003-05-31 17:51   ` Re: why Sequencial Scan when selecting on primary key of table? Rod Taylor <rbt@rbt.ca>
@ 2003-05-31 18:12     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2003-05-31 18:12 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: Brian Tarbox <btarbox@theworld.com>; pgsql-performance

Rod Taylor <rbt@rbt.ca> writes:
> Out of curiosity, why don't we confirm the unquoted value is an integer,
> numeric, etc, then change it into type 'unknown'?

UNKNOWNNUMERIC is one of the ideas that's been proposed, but it's not
clear to me that it is better than other alternatives.  In particular,
I don't like losing knowledge of the form and size of the constant.
Something like "WHERE int4col = 4.8" should yield FALSE, not "ERROR:
pg_atoi: unable to parse '4.8'" which is what you're likely to get with
a naive "unknown numeric type" approach.  A perhaps-more-realistic
objection is that it only solves the problem for trivial "var = const"
cases.  As soon as you look at even slightly more complicated
expressions, it stops doing much good.

I'm still of the opinion that the best solution in the long run is to
get rid of most of the cross-datatype numeric operators, but there are
pitfalls there too.  See last thread on the issue:
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php

			regards, tom lane



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


end of thread, other threads:[~2003-05-31 18:12 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-31 16:30 why Sequencial Scan when selecting on primary key of table? Brian Tarbox <btarbox@theworld.com>
2003-05-31 17:02 ` Andrew Sullivan <andrew@libertyrms.info>
2003-05-31 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-31 17:45   ` Brian Tarbox <btarbox@theworld.com>
2003-05-31 17:55     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-31 18:02     ` Andrew Sullivan <andrew@libertyrms.info>
2003-05-31 17:51   ` Rod Taylor <rbt@rbt.ca>
2003-05-31 18:12     ` Tom Lane <tgl@sss.pgh.pa.us>

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