pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedprimary key scans in sequence
8+ messages / 6 participants
[nested] [flat]
* primary key scans in sequence
@ 2001-03-29 13:47 bernd <bernd@matrixware.at>
0 siblings, 2 replies; 8+ messages in thread
From: bernd @ 2001-03-29 13:47 UTC (permalink / raw)
To: pgsql-sql
hey i have the following table def (834.000 rows, vaccum analyze'd):
dl_online=# \d mitglied
Table "mitglied"
Attribute | Type | Modifier
----------------+--------------+----------------------------
mitgliedid | bigint | not null
dlnummer | varchar(30) |
vorname | varchar(50) |
zuname | varchar(50) | not null
geburtsdatum | varchar(20) |
aktiv | boolean | not null default 't'::bool
strasse | varchar(255) |
plz | varchar(25) |
ort | varchar(255) |
telefon | varchar(255) |
eintrittsdatum | varchar(20) |
geschlechtid | bigint | not null default 3
treuelevelid | bigint | not null default 1
clubmitglied | boolean | not null default 'f'::bool
bemerkungen | text |
mid | bigint |
Indices: mitglied_dlnummer_idx, [on dlnummer]
mitglied_pkey [on mitgliedid]
--------------------
ok; i use 2 querys:
1) get one row over dlnummer:
dl_online=# explain select * from mitglied where dlnummer = '098765432';
NOTICE: QUERY PLAN:
Index Scan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1
width=154)
EXPLAIN
2) get one row over the primatry key (mitgliedid):
dl_online=# explain select * from mitglied where mitgliedid = 833228;
NOTICE: QUERY PLAN:
Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154)
EXPLAIN
why doesn't use postrges in (2) the primary-key-index?? take a look at the
cost! and both queries returns only ONE row (the optimizer knows that fact).
and the worst. in(2) the query take ~3sec. in this time the cpu works on
99.9% (rh-linux 7 on a compac dc10 -alpha).
the other works fine (no time to calculate, "no" use of the cpu!).
PS: i tried also "set ENABLE_SEQSCAN to OFF". no chance, (2) will work with
Seq Scan.
thx to ANY hint!!
bernd.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary key scans in sequence
@ 2001-03-30 16:11 Richard Poole <richard.poole@vi.net>
parent: bernd <bernd@matrixware.at>
1 sibling, 0 replies; 8+ messages in thread
From: Richard Poole @ 2001-03-30 16:11 UTC (permalink / raw)
To: bernd <bernd@matrixware.at>; +Cc: pgsql-sql
On Thu, Mar 29, 2001 at 03:47:58PM +0200, bernd wrote:
> hey i have the following table def (834.000 rows, vaccum analyze'd):
> dl_online=# \d mitglied
> Table "mitglied"
> Attribute | Type | Modifier
> ----------------+--------------+----------------------------
> mitgliedid | bigint | not null
> dlnummer | varchar(30) |
> vorname | varchar(50) |
> zuname | varchar(50) | not null
> geburtsdatum | varchar(20) |
> aktiv | boolean | not null default 't'::bool
> strasse | varchar(255) |
> plz | varchar(25) |
> ort | varchar(255) |
> telefon | varchar(255) |
> eintrittsdatum | varchar(20) |
> geschlechtid | bigint | not null default 3
> treuelevelid | bigint | not null default 1
> clubmitglied | boolean | not null default 'f'::bool
> bemerkungen | text |
> mid | bigint |
>
> Indices: mitglied_dlnummer_idx, [on dlnummer]
> mitglied_pkey [on mitgliedid]
> --------------------
> ok; i use 2 querys:
>
> 1) get one row over dlnummer:
> dl_online=# explain select * from mitglied where dlnummer = '098765432';
> NOTICE: QUERY PLAN:
> Index Scan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1
> width=154)
> EXPLAIN
>
> 2) get one row over the primatry key (mitgliedid):
> dl_online=# explain select * from mitglied where mitgliedid = 833228;
> NOTICE: QUERY PLAN:
> Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154)
> EXPLAIN
>
> why doesn't use postrges in (2) the primary-key-index?? take a look at the
> cost! and both queries returns only ONE row (the optimizer knows that fact).
Because the type of the "mitgliedid" is "bigint", but the type of the
constant "833228" is "integer" (I think; certainly it isn't "bigint").
Postgres doesn't realise that it can use an index on a bigint to do
comparisons to an integer. If you explicitly cast the constant to a
bigint, it should be willing to do an index scan, like so:
select * from mitglied where mitgliedid = 833228::bigint
Yes, this is a bit unpleasant to have to in your client code, and no,
I don't know if there's a neater way to let Postgres know it can use
this index for this query. But what I've just described does work.
Richard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: primary key scans in sequence
@ 2001-03-30 16:18 Peter Eisentraut <peter_e@gmx.net>
parent: bernd <bernd@matrixware.at>
1 sibling, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2001-03-30 16:18 UTC (permalink / raw)
To: bernd <bernd@matrixware.at>; +Cc: pgsql-sql
bernd writes:
> hey i have the following table def (834.000 rows, vaccum analyze'd):
> dl_online=# \d mitglied
> Table "mitglied"
> Attribute | Type | Modifier
> ----------------+--------------+----------------------------
> mitgliedid | bigint | not null
> dlnummer | varchar(30) |
> vorname | varchar(50) |
> zuname | varchar(50) | not null
> geburtsdatum | varchar(20) |
> aktiv | boolean | not null default 't'::bool
> strasse | varchar(255) |
> plz | varchar(25) |
> ort | varchar(255) |
> telefon | varchar(255) |
> eintrittsdatum | varchar(20) |
> geschlechtid | bigint | not null default 3
> treuelevelid | bigint | not null default 1
> clubmitglied | boolean | not null default 'f'::bool
> bemerkungen | text |
> mid | bigint |
>
> Indices: mitglied_dlnummer_idx, [on dlnummer]
> mitglied_pkey [on mitgliedid]
> --------------------
> 2) get one row over the primatry key (mitgliedid):
> dl_online=# explain select * from mitglied where mitgliedid = 833228;
> NOTICE: QUERY PLAN:
> Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154)
> EXPLAIN
This is because the system is not smart enough to match up a bigint =
integer comparison with an index scan on a bigint column. Quoting the
number, '833228', should fool it sufficiently to make this work.
Incidentally, it seems unlikely that you need to use bigint for membership
ids, unless you plan on more than 2*10^9 members.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: primary key scans in sequence
@ 2001-05-30 15:57 Koen Antonissen <Koen@Cee-Kay.net>
0 siblings, 1 reply; 8+ messages in thread
From: Koen Antonissen @ 2001-05-30 15:57 UTC (permalink / raw)
To: Richard Poole <richard.poole@vi.net>; bernd <bernd@matrixware.at>; +Cc: pgsql-sql
I have the same problem, my primary key is defined as a serial though.
Other tables use tables are defined as serials as well, but DO use Index
Scans
some tables do, some tables don't, even when creating 'my own' index on
the primary key, it still uses sequencial scans!
This one works fine:
Table "sponsors"
Attribute | Type | Modifier
-----------+---------+--------------------------------------------------
---
id | integer | not null default nextval('sponsors_id_seq'::text)
name | text | not null
email | text |
logo | text | not null default
'images/sponsors/logo_default.gif'
url | text |
qoute | text |
active | boolean | default 't'::bool
main | boolean | default 'f'::bool
Indices: index_sponsors_main,
index_sponsors_name,
sponsors_pkey,
unq_sponosrs_name
dsc_competition=# explain select * from sponsors where id = 4;
NOTICE: QUERY PLAN:
Index Scan using sponsors_pkey on sponsors (cost=0.00..2.01 rows=1
width=66)
EXPLAIN
Now this one doesn't:
Table "teams"
Attribute | Type | Modifier
-----------+---------+--------------------------------------------------
id | integer | not null default nextval('teams_id_seq'::text)
name | text | not null
mgr_name | text |
address | text |
zipcode | text |
city | text |
country | text |
email | text |
telnr | text |
mobnr | text |
faxnr | text |
logo | text | not null default 'images/teams/logo_default.gif'
movie | text |
url | text |
qoute | text |
active | boolean | default 't'::bool
Indices: index_teams_id, <=!!! 'my own' index
index_teams_name,
teams_pkey, <=normal pkey index
unq_teams_name
NOTICE: QUERY PLAN:
Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
EXPLAIN
I really don't understand the difference between the two, and it didn't
work before i created an extra index on id...
Kind regards,
Koen Antonissen
-----Original Message-----
From: Richard Poole [mailto:richard.poole@vi.net]
Sent: vrijdag 30 maart 2001 18:12
To: bernd
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] primary key scans in sequence
Because the type of the "mitgliedid" is "bigint", but the type of the
constant "833228" is "integer" (I think; certainly it isn't "bigint").
Postgres doesn't realise that it can use an index on a bigint to do
comparisons to an integer. If you explicitly cast the constant to a
bigint, it should be willing to do an index scan, like so:
select * from mitglied where mitgliedid = 833228::bigint
Yes, this is a bit unpleasant to have to in your client code, and no,
I don't know if there's a neater way to let Postgres know it can use
this index for this query. But what I've just described does work.
Richard
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: primary key scans in sequence
@ 2001-05-30 17:46 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Koen Antonissen <Koen@Cee-Kay.net>
0 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2001-05-30 17:46 UTC (permalink / raw)
To: Koen Antonissen <Koen@Cee-Kay.net>; +Cc: Richard Poole <richard.poole@vi.net>; bernd <bernd@matrixware.at>; pgsql-sql
On Wed, 30 May 2001, Koen Antonissen wrote:
> Now this one doesn't:
> Table "teams"
> Attribute | Type | Modifier
> -----------+---------+--------------------------------------------------
> id | integer | not null default nextval('teams_id_seq'::text)
> name | text | not null
> mgr_name | text |
> address | text |
> zipcode | text |
> city | text |
> country | text |
> email | text |
> telnr | text |
> mobnr | text |
> faxnr | text |
> logo | text | not null default 'images/teams/logo_default.gif'
> movie | text |
> url | text |
> qoute | text |
> active | boolean | default 't'::bool
> Indices: index_teams_id, <=!!! 'my own' index
> index_teams_name,
> teams_pkey, <=normal pkey index
> unq_teams_name
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
Looking at that cost, I have to wonder, how many rows are in the table
and has vacuum analyze been run on it? If the number of rows in the
table is small, the seq scan is definately better than having to read
from both the index and heap.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: primary key scans in sequence
@ 2001-05-31 09:03 Koen Antonissen <Koen@Cee-Kay.net>
0 siblings, 1 reply; 8+ messages in thread
From: Koen Antonissen @ 2001-05-31 09:03 UTC (permalink / raw)
To: bernd pinter <bernd@uptime.at>; +Cc: pgsql-sql
actually the serials are declared int4 (integer),
I tried to use your work around anyway, but it didn't work...:
dsc_competition=# \d classes
Table "classes"
Attribute | Type | Modifier
------------+---------+-------------------------------------------------
-
id | integer | not null default nextval('classes_id_seq'::text)
name | text | not null
definition | text |
active | boolean | default 't'::bool
Indices: classes_pkey,
index_classes_name,
unq_classes_name
dsc_competition=# explain select * from classes where id = int8(4);
NOTICE: QUERY PLAN:
Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
EXPLAIN
dsc_competition=# explain select * from classes where id = int4(4);
NOTICE: QUERY PLAN:
Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
EXPLAIN
dsc_competition=# explain select * from classes where id = int2(4);
NOTICE: QUERY PLAN:
Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
EXPLAIN
Thing I descovered after i posted to the group was that after creating
the scheme again, the indexes are used! after vacuum (analyze) the use
of indexes was gone again on certain tables...
Any other suggestions?
-----Original Message-----
From: bernd pinter [mailto:bernd@uptime.at]
Sent: donderdag 31 mei 2001 8:53
To: Koen Antonissen
Subject: Re: [SQL] primary key scans in sequence
the problem is the optimizer.
you use a int8 as primary key.
so if you do somthing like
"select * from sponsors where id = 34;"
then the optimizer interprets the 34 as an int4 => id is int8, so
postgres cant use the primary
key-index.
the workaround is simple. say that you are lookiong for an int8 (instead
of an int4):
"select * from sponsors where id = int8(34);"
thats it! now you convert 34 to an int8, postgres can use the
index......
bernd.
Koen Antonissen wrote:
> I have the same problem, my primary key is defined as a serial though.
> Other tables use tables are defined as serials as well, but DO use
Index
> Scans
> some tables do, some tables don't, even when creating 'my own' index
on
> the primary key, it still uses sequencial scans!
>
>
> This one works fine:
> Table "sponsors"
> Attribute | Type | Modifier
>
>
-----------+---------+--------------------------------------------------
> ---
> id | integer | not null default
nextval('sponsors_id_seq'::text)
> name | text | not null
> email | text |
> logo | text | not null default
> 'images/sponsors/logo_default.gif'
> url | text |
> qoute | text |
> active | boolean | default 't'::bool
> main | boolean | default 'f'::bool
> Indices: index_sponsors_main,
> index_sponsors_name,
> sponsors_pkey,
> unq_sponosrs_name
>
> dsc_competition=# explain select * from sponsors where id = 4;
> NOTICE: QUERY PLAN:
>
> Index Scan using sponsors_pkey on sponsors (cost=0.00..2.01 rows=1
> width=66)
>
> EXPLAIN
>
> Now this one doesn't:
> Table "teams"
> Attribute | Type | Modifier
>
-----------+---------+--------------------------------------------------
> id | integer | not null default nextval('teams_id_seq'::text)
> name | text | not null
> mgr_name | text |
> address | text |
> zipcode | text |
> city | text |
> country | text |
> email | text |
> telnr | text |
> mobnr | text |
> faxnr | text |
> logo | text | not null default
'images/teams/logo_default.gif'
> movie | text |
> url | text |
> qoute | text |
> active | boolean | default 't'::bool
> Indices: index_teams_id, <=!!! 'my own' index
> index_teams_name,
> teams_pkey, <=normal pkey index
> unq_teams_name
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
>
> EXPLAIN
>
>
> I really don't understand the difference between the two, and it
didn't
> work before i created an extra index on id...
>
> Kind regards,
> Koen Antonissen
>
>
> -----Original Message-----
> From: Richard Poole [mailto:richard.poole@vi.net]
> Sent: vrijdag 30 maart 2001 18:12
> To: bernd
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] primary key scans in sequence
>
>
> Because the type of the "mitgliedid" is "bigint", but the type of the
> constant "833228" is "integer" (I think; certainly it isn't "bigint").
> Postgres doesn't realise that it can use an index on a bigint to do
> comparisons to an integer. If you explicitly cast the constant to a
> bigint, it should be willing to do an index scan, like so:
>
> select * from mitglied where mitgliedid = 833228::bigint
>
> Yes, this is a bit unpleasant to have to in your client code, and no,
> I don't know if there's a neater way to let Postgres know it can use
> this index for this query. But what I've just described does work.
>
> Richard
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: primary key scans in sequence
@ 2001-05-31 15:37 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Koen Antonissen <Koen@Cee-Kay.net>
0 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2001-05-31 15:37 UTC (permalink / raw)
To: Koen Antonissen <Koen@Cee-Kay.net>; +Cc: pgsql-sql
It really depends on the number of rows. If the number of
rows in the tables are small or the number of rows returned is
a reasonable percentage, the index scan is currently more expensive.
What does (for example) select count(*) from classes; give?
On Thu, 31 May 2001, Koen Antonissen wrote:
> actually the serials are declared int4 (integer),
> I tried to use your work around anyway, but it didn't work...:
>
> dsc_competition=# \d classes
> Table "classes"
> Attribute | Type | Modifier
>
> ------------+---------+-------------------------------------------------
> -
> id | integer | not null default nextval('classes_id_seq'::text)
> name | text | not null
> definition | text |
> active | boolean | default 't'::bool
> Indices: classes_pkey,
> index_classes_name,
> unq_classes_name
>
> dsc_competition=# explain select * from classes where id = int8(4);
> NOTICE: QUERY PLAN:
>
> Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
>
> EXPLAIN
> dsc_competition=# explain select * from classes where id = int4(4);
> NOTICE: QUERY PLAN:
>
> Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
>
> EXPLAIN
> dsc_competition=# explain select * from classes where id = int2(4);
> NOTICE: QUERY PLAN:
>
> Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
>
> EXPLAIN
>
>
>
> Thing I descovered after i posted to the group was that after creating
> the scheme again, the indexes are used! after vacuum (analyze) the use
> of indexes was gone again on certain tables...
>
> Any other suggestions?
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: primary key scans in sequence
@ 2001-05-31 23:26 Gerald Gutierrez <gml1@coldresist.com>
0 siblings, 0 replies; 8+ messages in thread
From: Gerald Gutierrez @ 2001-05-31 23:26 UTC (permalink / raw)
To: Koen Antonissen <Koen@Cee-Kay.net>; bernd pinter <bernd@uptime.at>; +Cc: pgsql-sql
I think you're just witnessing the optimizer at work. If it thinks that
doing sequential scans is faster, it will ignore the indices.
At 11:03 AM 5/31/2001 +0200, Koen Antonissen wrote:
>Thing I descovered after i posted to the group was that after creating
>the scheme again, the indexes are used! after vacuum (analyze) the use
>of indexes was gone again on certain tables...
>
>Any other suggestions?
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-05-31 23:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-29 13:47 primary key scans in sequence bernd <bernd@matrixware.at>
2001-03-30 16:11 ` Richard Poole <richard.poole@vi.net>
2001-03-30 16:18 ` Peter Eisentraut <peter_e@gmx.net>
2001-05-30 15:57 RE: primary key scans in sequence Koen Antonissen <Koen@Cee-Kay.net>
2001-05-30 17:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-05-31 09:03 RE: primary key scans in sequence Koen Antonissen <Koen@Cee-Kay.net>
2001-05-31 15:37 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-05-31 23:26 RE: primary key scans in sequence Gerald Gutierrez <gml1@coldresist.com>
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