Обсуждение: Poor performance on very simple query ?
Hi List !
I have a performance problem, but I am not sure whether it really
is a problem or not.
I am running a fresh install of PostgreSQL 8.1.4 on Windows2000.
The server is a bi-opteron with 2GB of RAM. The PostgreSQL's data
folder is on a RAID-0 array of 2 SATA WD Raptor drives (10.000
rpm, 8MB cache).
I have a very simple table, with only ~500 rows :
CREATE TABLE table1
(
gid int4 NOT NULL DEFAULT 0,
field1 varchar(45) NOT NULL,
field2 int2 NOT NULL DEFAULT 1,
field3 int2 NOT NULL DEFAULT 0,
field4 int2 NOT NULL DEFAULT 1,
field5 int4 NOT NULL DEFAULT -1,
field6 int4,
field7 int4,
field8 int4,
field9 int2 DEFAULT 1,
CONSTRAINT table1_pkey PRIMARY KEY (gid)
)
WITHOUT OIDS;
The problem is that simple select queries with the primary key in the
WHERE statement take very long to run.
For example, this query returns only 7 rows and takes about 1
second to run !
SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13,7,68,41);
EXPLAIN ANALYZE SELECT * FROM table1 WHERE gid in
(33,110,65,84,92,94,13,7,68,41);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on table1 (cost=0.00..23.69 rows=10 width=35) (actual
time=0.023..0.734 rows=7 loops=1)
Filter: ((gid = 33) OR (gid = 110) OR (gid = 65) OR (gid = 84)
OR (gid = 92) OR (gid = 94) OR (gid = 13) OR (gid = 7) OR (gid =
68) OR (gid = 41))
Total runtime: 0.801 ms
(3 rows)
I have run "VACUUM FULL" on this table many times... I don't know
what to try next !
What is wrong here (because I hope that something is wrong) ?
Thanks a lot for your help !
Regards
--
Arnaud
On Tue, Oct 03, 2006 at 01:25:10PM +0200, Arnaud Lesauvage wrote: > For example, this query returns only 7 rows and takes about 1 > second to run ! > > [...] > > Total runtime: 0.801 ms 0.801 ms is _far_ under a second... Where do you have the latter timing from? /* Steinar */ -- Homepage: http://www.sesse.net/
Steinar H. Gunderson wrote: >> Total runtime: 0.801 ms > > 0.801 ms is _far_ under a second... Where do you have the latter timing from? I fell stupid... Sorry for the useless message... ---->[]
On Oct 3, 2006, at 13:25 , Arnaud Lesauvage wrote: > The problem is that simple select queries with the primary key in > the WHERE statement take very long to run. > For example, this query returns only 7 rows and takes about 1 > second to run ! > SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13,7,68,41); This is a very small table, but generally speaking, such queries benefit from an index; eg., create index table1_gid on table1 (gid); Note that PostgreSQL may still perform a sequential scan if it thinks this has a lower cost, eg. for small tables that span just a few pages. > I have run "VACUUM FULL" on this table many times... I don't know > what to try next ! PostgreSQL's query planner relies on table statistics to perform certain optimizations; make sure you run "analyze table1". Alexander.
[Arnaud Lesauvage - Tue at 01:25:10PM +0200] > I have a performance problem, but I am not sure whether it really > is a problem or not. > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------ > Seq Scan on table1 (cost=0.00..23.69 rows=10 width=35) (actual > time=0.023..0.734 rows=7 loops=1) > Filter: ((gid = 33) OR (gid = 110) OR (gid = 65) OR (gid = 84) > OR (gid = 92) OR (gid = 94) OR (gid = 13) OR (gid = 7) OR (gid = > 68) OR (gid = 41)) > Total runtime: 0.801 ms > (3 rows) > > I have run "VACUUM FULL" on this table many times... I don't know > what to try next ! > What is wrong here (because I hope that something is wrong) ? > Thanks a lot for your help ! Did you try "analyze" as well? It's weird it's using seq scan, since you have a primary key it's supposed to have an index ... though 500 rows is little. I just checked up our own production database, takes 0.08 ms to fetch a row by ID from one of our tables containing 176k with rows.
[Tobias Brox - Tue at 02:10:04PM +0200] > Did you try "analyze" as well? It's weird it's using seq scan, since > you have a primary key it's supposed to have an index ... though 500 > rows is little. > > I just checked up our own production database, takes 0.08 ms to fetch a > row by ID from one of our tables containing 176k with rows. Oh, the gid is not primary key. I guess I should also apologize for adding noise here :-) Make an index here! :-)
Tobias Brox wrote: > Oh, the gid is not primary key. I guess I should also apologize for > adding noise here :-) Yes, it is a primary key, but I am the noise maker here ! ;-)
[Arnaud Lesauvage - Tue at 02:13:59PM +0200] > Tobias Brox wrote: > >Oh, the gid is not primary key. I guess I should also apologize for > >adding noise here :-) > > Yes, it is a primary key, but I am the noise maker here ! ;-) Oh - it is. How can you have a default value on a primary key? Will it use the index if you do "analyze"? Is there an index on the table at all, do you get it up if you ask for a description of the table (\d tablename)?
Tobias Brox wrote: > [Arnaud Lesauvage - Tue at 02:13:59PM +0200] >> Tobias Brox wrote: >> >Oh, the gid is not primary key. I guess I should also apologize for >> >adding noise here :-) >> >> Yes, it is a primary key, but I am the noise maker here ! ;-) > > Oh - it is. How can you have a default value on a primary key? Good question, but I am not the DB designer in that case. > Will it > use the index if you do "analyze"? Is there an index on the table at > all, do you get it up if you ask for a description of the table (\d > tablename)? In this case (a simplified version of the real case), the pkey is the only index. It is used if I only as for one row (WHERE gid=33).
Tobias Brox <tobias 'at' nordicbet.com> writes:
> Oh - it is. How can you have a default value on a primary key? Will it
you can but it is useless :)
foo=# create table bar (uid int primary key default 0, baz text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"
CREATE TABLE
foo=# insert into bar (baz) values ('');
INSERT 217426996 1
foo=# insert into bar (baz) values ('');
ERROR: duplicate key violates unique constraint "bar_pkey"
--
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/
Arnaud Lesauvage <thewild@freesurf.fr> writes:
> Seq Scan on table1 (cost=0.00..23.69 rows=10 width=35) (actual
> time=0.023..0.734 rows=7 loops=1)
> Filter: ((gid = 33) OR (gid = 110) OR (gid = 65) OR (gid = 84)
> OR (gid = 92) OR (gid = 94) OR (gid = 13) OR (gid = 7) OR (gid =
> 68) OR (gid = 41))
> Total runtime: 0.801 ms
This will start using the index as soon as the table gets big enough to
make it worthwhile.
regards, tom lane
On October 3, 2006 04:25 am, Arnaud Lesauvage wrote: > Hi List ! > > I have a performance problem, but I am not sure whether it really > is a problem or not. > I am running a fresh install of PostgreSQL 8.1.4 on Windows2000. > The server is a bi-opteron with 2GB of RAM. The PostgreSQL's data > folder is on a RAID-0 array of 2 SATA WD Raptor drives (10.000 > rpm, 8MB cache). > > I have a very simple table, with only ~500 rows : > CREATE TABLE table1 > ( > gid int4 NOT NULL DEFAULT 0, > field1 varchar(45) NOT NULL, > field2 int2 NOT NULL DEFAULT 1, > field3 int2 NOT NULL DEFAULT 0, > field4 int2 NOT NULL DEFAULT 1, > field5 int4 NOT NULL DEFAULT -1, > field6 int4, > field7 int4, > field8 int4, > field9 int2 DEFAULT 1, > CONSTRAINT table1_pkey PRIMARY KEY (gid) > ) > WITHOUT OIDS; > > The problem is that simple select queries with the primary key in the > WHERE statement take very long to run. > For example, this query returns only 7 rows and takes about 1 > second to run ! According to your explain analyze, it's taking 0.8 of a milisecond (less than 1 1000th of a second) so I can't see how this can possibly be speed up. > SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13,7,68,41); > > EXPLAIN ANALYZE SELECT * FROM table1 WHERE gid in > (33,110,65,84,92,94,13,7,68,41); > > QUERY PLAN > --------------------------------------------------------------------------- >--------------------------------------------------------------------------- > Seq Scan on table1 (cost=0.00..23.69 rows=10 width=35) (actual > time=0.023..0.734 rows=7 loops=1) > Filter: ((gid = 33) OR (gid = 110) OR (gid = 65) OR (gid = 84) > OR (gid = 92) OR (gid = 94) OR (gid = 13) OR (gid = 7) OR (gid = > 68) OR (gid = 41)) > Total runtime: 0.801 ms > (3 rows) > > I have run "VACUUM FULL" on this table many times... I don't know > what to try next ! > What is wrong here (because I hope that something is wrong) ? > Thanks a lot for your help ! > > Regards > -- > Arnaud > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Darcy Buskermolen Command Prompt, Inc. Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/
On October 3, 2006 05:08 am, Alexander Staubo wrote: > On Oct 3, 2006, at 13:25 , Arnaud Lesauvage wrote: > > The problem is that simple select queries with the primary key in > > the WHERE statement take very long to run. > > For example, this query returns only 7 rows and takes about 1 > > second to run ! > > SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13,7,68,41); > > This is a very small table, but generally speaking, such queries > benefit from an index; eg., > > create index table1_gid on table1 (gid); gid is is a PRIMARY KEY, so it will already have an index in place. > > Note that PostgreSQL may still perform a sequential scan if it thinks > this has a lower cost, eg. for small tables that span just a few pages. > > > I have run "VACUUM FULL" on this table many times... I don't know > > what to try next ! > > PostgreSQL's query planner relies on table statistics to perform > certain optimizations; make sure you run "analyze table1". > > Alexander. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Darcy Buskermolen Command Prompt, Inc. Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/
Hi, Tobias,
Tobias Brox wrote:
> How can you have a default value on a primary key?
Just declare the column with both a default value and a primary key
constraint.
It makes sense when the default value is calculated instead of a
constant, by calling a function that generates the key.
In fact, the SERIAL type does nothing but defining a sequence, and then
use nextval('sequencename') as default.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org