Re: [PERFORM] Poor performance on very simple query ?
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 TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
---(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
[PERFORM] 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 ---(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
Re: [PERFORM] Poor performance on very simple query ?
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... [] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Poor performance on very simple query ?
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Poor performance on very simple query ?
[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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Poor performance on very simple query ?
[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! :-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Poor performance on very simple query ?
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 ! ;-) ---(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
Re: [PERFORM] Poor performance on very simple query ?
[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)? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Poor performance on very simple query ?
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). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Poor performance on very simple query ?
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/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] Poor performance on very simple query ?
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Poor performance on very simple query ?
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 [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
