[BUGS] low performance
I am running the precomplied binary of Postgreql 7.1.2 on a Redhat 7.1 (on a Dual
Celeron System with 256MB, kernel 2.4.4 and 2.4.5) System.
(The installation of the new 7.1.3 doesn't seem to solve the problem)
I am connecting to the DB with a Perl Program (using Perl 5.6.0 with DBD-Pg-1.01 and
DBI-1.19).
The program inserts some million rows into a db with about 30 tables. The processing
takes (if everyting works fine) about 10 hours to complete. Usually the my Perl-Script
and the database share the available CPU time 50:50.
But sometimes the database is very slow eating up most (>98%) of the available CPU
time.
(Of cause I know VACUUM and VACUUM ANALYZE, this is not the problem).
The only thing that seems to help then, is killing the perl script, stopping
postgresql, running "ipcclean", and start again from the beginning. If it works from
the beginning, the database is ususally very fast until all data are processed.
But if someone else connects (using psql), sometimes the database gets very slow until
it is using all the CPU time.
There are no error messages at postgres-startup.
I already increased the number of buffers to 2048 (doesn't help)
I cannot reproduce these problems, sometimes the db is fast, sometimes very slow. The
perl script doesn't seem to be the problem, because I wrote all SQL Commands to a file
and processed them later ("psql dbname postgres < SQL-File").
Same thing: sometimes slow sometimes fast.
Andreas
---(end of broadcast)---
TIP 3: 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
[BUGS] low performance
I am running the precomplied binary of Postgreql 7.1.2 on a Redhat 7.1 (on a Dual
Celeron System with 256MB, kernel 2.4.4 and 2.4.5) System.
(The installation of the new 7.1.3 doesn't seem to solve the problem)
I am connecting to the DB with a Perl Program (using Perl 5.6.0 with DBD-Pg-1.01 and
DBI-1.19).
The program inserts some million rows into a db with about 30 tables. The processing
takes (if everyting works fine) about 10 hours to complete. Usually the my Perl-Script
and the database share the available CPU time 50:50.
But sometimes the database is very slow eating up most (>98%) of the available CPU
time.
(Of cause I know VACUUM and VACUUM ANALYZE, this is not the problem).
The only thing that seems to help then, is killing the perl script, stopping
postgresql, running "ipcclean", and start again from the beginning. If it works from
the beginning, the database is ususally very fast until all data are processed.
But if someone else connects (using psql), sometimes the database gets very slow until
it is using all the CPU time.
There are no error messages at postgres-startup.
I already increased the number of buffers to 2048 (doesn't help)
I cannot reproduce these problems, sometimes the db is fast, sometimes very slow. The
perl script doesn't seem to be the problem, because I wrote all SQL Commands to a file
and processed them later ("psql dbname postgres < SQL-File").
Same thing: sometimes slow sometimes fast.
Andreas
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [BUGS] low performance
It is running on many transactions. At least after 5 inserts a transaction is commited. The filesystems doesn't get slow (reading a (big) file works still at >20 MBytes/s). 14839 postgres 20 0 19948 19M 18980 R98.5 7.7 477:24 postmaster 14819 postgres 8 0 1856 1856 1700 S 0.0 0.7 0:00 postmaster 14838 andreas9 0 15228 14M 1796 S 0.7 5.9 11:58 parse.pl The main postmaster is job 14819 (0.0% CPU). The postmaster spawned by perl is sucking up 98.5% CPU. cat /proc/meminfo writes: total:used:free: shared: buffers: cached: Mem: 261959680 260149248 18104320 6115328 129863680 Swap: 133885952 204800 133681152 MemTotal: 255820 kB MemFree: 1768 kB MemShared: 0 kB Buffers: 5972 kB Cached: 126820 kB Active: 38432 kB Inact_dirty: 83408 kB Inact_clean: 10952 kB Inact_target: 520 kB HighTotal: 0 kB HighFree:0 kB LowTotal: 255820 kB LowFree: 1768 kB SwapTotal: 130748 kB SwapFree: 130548 kB On Mon, 20 Aug 2001 10:28:04 -0700 (MST) grant <[EMAIL PROTECTED]> wrote: > Is this running as one transaction, or is it not a transaction? Have you > tried committing every 10,000 or so if it is in one transaction? It could > be a logging problem with the transaction being too big. > > Does the file system as a whole get slow, or just Postgres? Is it one > connection, or does it disconnect and reconnect a lot? > > Is it the main postmaster sucking up all the CPU, or the one spawned by > the PERL, or the one spawned by psql? > > How much do the file system cache and io buffers grow? > __ > > Your mouse has moved. >You must restart Windows for your changes to take effect. > > #!/usr/bin/perl > print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10); > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Re: low performance
I am aware of the performance drawbacks because of indices and triggers. In fact I
have a trigger and an index on the most populated table.
It is not possible in my case to remove the primary keys during insert, because the
database structure and foreign keys validate my data during import.
The problem is, that sometimes the performance is good, and sometimes the database is
awfully slow.
If it is slow, postgres is eating up all CPU time and it takes at least 150 times
longer to insert the data.
I don't know why and what to do against that.
Andreas
On Mon, 20 Aug 2001 19:39:31 -0400
Jonas Lindholm <[EMAIL PROTECTED]> wrote:
> Do you have any index on the tables ? Any triggers ?
>
> If you want to insert 1 million rows you should drop the index, insert the data and
>then recreate the index.
> You should also try the COPY command to insert the data.
>
> You should also avoid having anyone to connect to the database when you insert a lot
>of rows, and 1 million rows are a lot of rows for any database.
>
> I've been able to insert, in one table, 17 million record in ~3 hours on a Compaq
>SMP 750 Mhz with 512MB
> by dropping the index, using several COPY commands at the same time loading
>different parts of the data and then creating the index again.
> At the time of the inserts no other processes than the COPY's was connected to the
>database.
>
> /Jonas Lindholm
>
>
> Andreas Wernitznig wrote:
>
> > I am running the precomplied binary of Postgreql 7.1.2 on a Redhat 7.1 (on a Dual
>Celeron System with 256MB, kernel 2.4.4 and 2.4.5) System.
> > (The installation of the new 7.1.3 doesn't seem to solve the problem)
> >
> > I am connecting to the DB with a Perl Program (using Perl 5.6.0 with DBD-Pg-1.01
>and DBI-1.19).
> > The program inserts some million rows into a db with about 30 tables. The
>processing takes (if everyting works fine) about 10 hours to complete. Usually the my
>Perl-Script and the database share the available CPU time 50:50.
> > But sometimes the database is very slow eating up most (>98%) of the available CPU
>time.
> > (Of cause I know VACUUM and VACUUM ANALYZE, this is not the problem).
> >
> > The only thing that seems to help then, is killing the perl script, stopping
>postgresql, running "ipcclean", and start again from the beginning. If it works from
>the beginning, the database is ususally very fast until all data are processed.
> >
> > But if someone else connects (using psql), sometimes the database gets very slow
>until it is using all the CPU time.
> >
> > There are no error messages at postgres-startup.
> > I already increased the number of buffers to 2048 (doesn't help)
> >
> > I cannot reproduce these problems, sometimes the db is fast, sometimes very slow.
>The perl script doesn't seem to be the problem, because I wrote all SQL Commands to a
>file and processed them later ("psql dbname postgres < SQL-File").
> > Same thing: sometimes slow sometimes fast.
> >
> > Andreas
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Re: low performance
I took option 1 and managed to create a profile of a slow and a fast run: The frequent functions of the FAST run: % cumulative self self total time seconds secondscalls Ts/call Ts/call name 0.00 0.00 0.00 15725437 0.00 0.00 AllocSetAlloc 0.00 0.00 0.00 15380742 0.00 0.00 MemoryContextAlloc 0.00 0.00 0.00 11296700 0.00 0.00 ExecEvalExpr 0.00 0.00 0.00 8276639 0.00 0.00 newNode 0.00 0.00 0.00 5430717 0.00 0.00 MemoryContextSwitchTo 0.00 0.00 0.00 4492641 0.00 0.00 LockBuffer 0.00 0.00 0.00 4425642 0.00 0.00 AllocSetFree 0.00 0.00 0.00 4356571 0.00 0.00 pfree 0.00 0.00 0.00 3873174 0.00 0.00 pq_getbyte 0.00 0.00 0.00 3799725 0.00 0.00 appendStringInfoChar The frequent functions of the SLOW run: % cumulative self self total time seconds secondscalls Ts/call Ts/call name 0.00 0.00 0.00 27832819 0.00 0.00 ExecEvalExpr 0.00 0.00 0.00 19040887 0.00 0.00 AllocSetAlloc 0.00 0.00 0.00 18976313 0.00 0.00 MemoryContextAlloc 0.00 0.00 0.00 18722462 0.00 0.00 LockBuffer 0.00 0.00 0.00 18684832 0.00 0.00 MemoryContextSwitchTo 0.00 0.00 0.00 18442039 0.00 0.00 pg_detoast_datum 0.00 0.00 0.00 16947638 0.00 0.00 AllocSetFree 0.00 0.00 0.00 16934648 0.00 0.00 pfree 0.00 0.00 0.00 9716164 0.00 0.00 SpinAcquire 0.00 0.00 0.00 9716164 0.00 0.00 SpinRelease Since these files are to big for a posting, I have put the whole profile files on: ftp://ftp.insilico.com/out.fast.gz ftp://ftp.insilico.com/out.slow.gz I don't know why the time column and number of seconds is zero in all the cases. I am using the Redhat 7.1 binutils (binutils-2.10.91.0.2-3). On Tue, 21 Aug 2001 17:38:23 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Andreas Wernitznig <[EMAIL PROTECTED]> writes: > > I am aware of the performance drawbacks because of indices and > > triggers. In fact I have a trigger and an index on the most populated > > table. It is not possible in my case to remove the primary keys > > during insert, because the database structure and foreign keys > > validate my data during import. > > Foreign keys eh? > > > The problem is, that sometimes the performance is good, and sometimes > > the database is awfully slow. If it is slow, postgres is eating up > > all CPU time and it takes at least 150 times longer to insert the > > data. I don't know why and what to do against that. > > We found some foreign-key-related performance problems not long ago, > and it could be you're happening on another one. However there's not > enough info here to figure it out. I can offer you two alternatives: > > 1. Compile up the backend with profiling enabled (if you're using gcc > then "make PROFILE=-pg clean all" in src/backend should do the trick). > Collect profiles for both a "normal" and a "slow" run and send them in. > > 2. Develop a self-contained example that exhibits the problem, and send > it along for someone else to profile. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Re: low performance
On Wed, 22 Aug 2001 19:19:42 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Andreas Wernitznig <[EMAIL PROTECTED]> writes: > > I took option 1 and managed to create a profile of a slow and a fast run: > > It's difficult to compare these profiles, because they seem to be taken > over very different numbers of queries --- did you let the "fast" run > process more queries than the "slow" one? Both runs where running for about 5 minutes. The fast one processes about 10 times more data than the slow one. After a longer time this ratio gets even worse. The databases contain about 13 rows before the start of the run. To make it more comparable I have made two additional runs, a slow and a fast one with exactly the same number of inserts (about 20500) and put it on our ftp server: ftp://ftp.insilico.com/def.fast.gz ftp://ftp.insilico.com/def.slow.gz The fast run takes about 5 minutes, the slow one took 59 minutes. *SLOW* Flat profile: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls Ts/call Ts/call name 100.00 0.01 0.01 reaper 0.00 0.01 0.00 555836881 0.00 0.00 ExecEvalExpr 0.00 0.01 0.00 372917548 0.00 0.00 LockBuffer 0.00 0.01 0.00 369206213 0.00 0.00 MemoryContextSwitchTo 0.00 0.01 0.00 366016306 0.00 0.00 pg_detoast_datum 0.00 0.01 0.00 342868028 0.00 0.00 AllocSetAlloc 0.00 0.01 0.00 342579484 0.00 0.00 MemoryContextAlloc 0.00 0.01 0.00 333623357 0.00 0.00 AllocSetFree 0.00 0.01 0.00 333565521 0.00 0.00 pfree 0.00 0.01 0.00 189738579 0.00 0.00 SpinAcquire 0.00 0.01 0.00 189738579 0.00 0.00 SpinRelease *FAST* Flat profile: Each sample counts as 0.01 seconds. no time accumulated % cumulative self self total time seconds secondscalls Ts/call Ts/call name 0.00 0.00 0.00 13439626 0.00 0.00 AllocSetAlloc 0.00 0.00 0.00 13151082 0.00 0.00 MemoryContextAlloc 0.00 0.00 0.00 8194227 0.00 0.00 ExecEvalExpr 0.00 0.00 0.00 6962789 0.00 0.00 newNode 0.00 0.00 0.00 4072271 0.00 0.00 MemoryContextSwitchTo 0.00 0.00 0.00 3931730 0.00 0.00 AllocSetFree 0.00 0.00 0.00 3873894 0.00 0.00 pfree 0.00 0.00 0.00 3389344 0.00 0.00 LockBuffer 0.00 0.00 0.00 3253378 0.00 0.00 pq_getbyte 0.00 0.00 0.00 3191526 0.00 0.00 appendStringInfoChar What does the first row of the slow run mean (reaper) ? > However, I think what is happening is that some queries are being done > as indexscans in the fast case and seqscans in the slow case. The > ratio of ExecIndexScan calls to ExecSeqScan calls is vastly different > in the two profiles. Does the new profiles proof that assumption ? > It looks like you are doing vacuums fairly frequently, so I speculate > that the statistics gathered by vacuum are changing just enough to alter > the planner's choice between indexscan and seqscan for some > often-executed query type. Evidently the planner is guessing the costs > to be nearly the same, so a small change in stats might tip the choice > --- but in reality the costs are quite a bit different, thus you observe > fast and slow behavior. In all of the profiled runs no "vacuum" was executed. Another question: When I am executing a run, does a vacuum with another postmaster influence the planner of the run ? (By the way: I didn't do that for the profiled samples). I am hardly executing any queries during the runs (only inserts). I am only running SELECTs on two tables with 600 and 200 rows, respectively. If I have a database with some data inside and run "vacuum" and "vacuum analyze" and then delete the data and start the run it is FAST. If I run "vacuum" and "vacuum analyze" on an empty database, the following run will be a SLOW one. It seems that the planner plans a "Seq Scan" if vacuum analyze was executed on a empty database and an "Index Scan" if vacuum analyze was executed on a full database. (as expected) > The next step should be to get EXPLAIN results for the queries used > by your application in both fast and slow states. This will help us > narrow down where the planner's misprediction is occurring. I build in some "explain select ..." on the most populated table in my parser script (Such a kind of select, I assume, is done by the pk trigger) and got a confirmation for the assumption above (Seq Scan vs. Index Scan). If I am in a slow
Re: [BUGS] PD: triggered data change violation on relation "tbl_b"
You cannot insert and delete the same data within one transaction. Only one change of a row is allowed. Greetings Andreas On Wed, 29 Aug 2001 13:18:02 +0200 "Pawel Pawlowski" <[EMAIL PROTECTED]> wrote: > When I insert to table new row and after this in the same transaction I delete this >row I get such error: > triggered data change violation on relation "tbl_b" > > I've created database using simple script: > > CREATE TABLE tbl_a > ( > pn_id SERIAL, > pn_a VARCHAR(400) NOT NULL, > PRIMARY KEY (pn_id) > ); > CREATE TABLE tbl_b > ( > pc_id INT4 NOT NULL REFERENCES tbl_a (pn_id) ON UPDATE CASCADE ON DELETE >CASCADE, > pc_b VARCHAR(40) NOT NULL, > PRIMARY KEY (pc_id, pc_b) > ); > INSERT INTO tbl_a VALUES (1, 'xxx'); > > And this is the sample script that I use to generete this bug: > > begin transaction; > insert into tbl_b values (1, 'xxx'); > delete from tbl_b where pc_id=1; > ERROR: triggered data change violation on relation "tbl_b" > > How to solve this problem ? -- Andreas Wernitznig Insilico Software GmbH E-Mail: [EMAIL PROTECTED] Web: www.insilico.com -- ---(end of broadcast)--- TIP 3: 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: [BUGS] Re: low performance
Yes, I understand very clearly what you mean. Maybe my mails were to confused, that's why I try to explain my problem once more: step 1. An empty table with a primary key (=index key) where an "explain" tells me, that a Seq Scan is used to SELECT a special row. step 2. Then I start to fill data into that table. step 3. Then I run a vacuum analyze to update the planner statistics. step 4. I run an "EXPLAIN select * from where = 999;" step 5. Then I fill in additional data. What I expect is, that from step 5 on the pk-trigger (I don't know if this mechanism that checks for uniqueness is really a trigger) uses the Index to check for possible double entries. Although "EXPLAIN" in step 4 pretend to use an Index Scan the data insert becomes slower and slower (>98% of the processor is used by a postmaster). All these steps are done with a single connection (postmaster). The only way to make it faster after step 3 is to close that connection (and stop that postmaster thread with it) and establish a new one. It seems like the planner (at least for pk checking) of an *established* connection to a database doesn't receive the information gained from "vacuum analyze". Greetings Andreas On Mon, 03 Sep 2001 12:26:39 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Andreas Wernitznig <[EMAIL PROTECTED]> writes: > > To make it more comparable I have made two additional runs, a slow and > > a fast one with exactly the same number of inserts (about 20500) and > > put it on our ftp server: > > >> However, I think what is happening is that some queries are being done > >> as indexscans in the fast case and seqscans in the slow case. The > >> ratio of ExecIndexScan calls to ExecSeqScan calls is vastly different > >> in the two profiles. > > > Does the new profiles proof that assumption ? > > Yes, see for yourself: > def.fast: > 0.00 0.00 0.0022481 0.00 0.00 ExecSeqScan > 0.00 0.00 0.0020161 0.00 0.00 ExecIndexScan > def.slow: > 0.00 0.01 0.0041940 0.00 0.00 ExecSeqScan > 0.00 0.01 0.00 702 0.00 0.00 ExecIndexScan > > So there are about 19500 queries that are being done as indexscans in > one case and seqscans in the other. > > > If I run "vacuum" and "vacuum analyze" on an empty database, the > > following run will be a SLOW one. > > The whole point of vacuum analyze is to give the planner some statistics > about the contents of the tables. Vacuum analyze when a table is empty > is useless (even counterproductive, if the table shortly thereafter > becomes large --- the planner will still think it is empty). > > regards, tom lane > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] backend closed the channel unexpectedly
This is the last part of a "vacuum verbose analyze;": NOTICE: --Relation pg_toast_17058-- NOTICE: Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 9: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 113, MaxLen 2034; Re-using: Free/Avai l. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. NOTICE: Index pg_toast_17058_idx: Pages 2; Tuples 9. CPU 0.00s/0.00u sec. NOTICE: Analyzing... pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. This usually happens after some millions of rows are inserted into the database. What can I do to avoid this error? Greetings Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #474: Index using problem
The query optimizer uses the index only if enough data are present in the table. If only a few data are available a Seq Scan performs better and is therefore used. Further one of the problems (which is hopfully solved in version 7.2) is that the query optimizer used for primary keys/foreign keys is not updated for an uninterrupted database connection. E.g.: If have an empty database "Seq Scan" is used in any case. Then I connect to the database, fill in some data, and execute an "VACUUM ANALYZE" (which updates the statistics for the query optimizer). If I don't disconnect from the database but use the first connection again, the database still uses the (now) slower "seq scan" for "primary/foreign key" checking. In this case the query optimizer statistics are not updated for established connections. My suggestion and question to the postgresql-programmers is now: Why don't you skip the automatic index creation for primary keys and let the user decide to create an index, that should be used in any case, regardless whether the table is full or empty ? Andreas On Fri, 5 Oct 2001 03:21:01 -0400 (EDT) [EMAIL PROTECTED] wrote: > JoE Olcsak ([EMAIL PROTECTED]) reports a bug with a severity of 4 > The lower the number the more severe it is. > > Short Description > Index using problem > > Long Description > I have a problem when using indexes under PostgreSQL 7.1.1 ... > > The problem is : > > I have a field of table ... This is an INT4 type field ... and I > created an index for this field like : > create index idx_of_field on cim(utod_cim_id); > ... and I executed a simple query ... > > explain select * from cim where utod_cim_id=0; > > NOTICE: QUERY PLAN: > > Seq Scan on cim (cost=0.00..6555.41 rows=253633 width=118) > > EXPLAIN > > The query optimizer does not use my index.. > > I'm executed vacuum analyze command for this table but this not helps me ... > > Then I created another index for this field: > > create index idx_of_field2 on cim(int4(utod_cim_id)); > > ... and I executed another simple query : > >explain select * from cim where int4(utod_cim_id)=0; >NOTICE: QUERY PLAN: > >Index Scan using idx_of_field2 on cim (cost=0.00..6499.70rows=2536 width=118) > >EXPLAIN > > What is wrong in the first case ? > > > > Sample Code > > > No file was uploaded with this report > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #474: Index using problem
1. I created a small database consisting of two connected tables: create table table1 ( index1int4not null, textfield varchar(1000) not null, constraint PK_table1 primary key (index1) ); create table table2 ( index2int4not null, index1int4not null, textfield varchar(1000) not null, constraint PK_table2 primary key (index2), constraint FK_table1 foreign key (index1) references table1 (index1) on delete restrict on update restrict ); 2. Then I insert 100 rows ($n=1..100) in each of these tables: insert into table1 VALUES ($n, '123456789'); insert into table2 VALUES ($n, $n, '123456789'); 3. then I send a "vacuum analyze" and an "explain select * from table1 where index1 = 543;" The output is: NOTICE: QUERY PLAN: Index Scan using pk_table1 on table1 (cost=0.00..2.01 rows=1 width=16) 4. Then I insert 4900 rows into each of these tables like in step 2. -- Test A: -- Then I send a "vacuum analyze;" and "delete from table2;", and "delete from table1;" and rerun steps 2 to 4 -> step 4 takes 39 seconds. -- Test B: -- Then I send "delete from table2;", and "delete from table1;", and a "vacuum analyze;" and rerun steps 2 to 4 -> step 4 takes 81 seconds. Although the "explain" command tells me that an Index Scan is used, step 4 is much slower in Test B. For both tests (steps 2-4) I use one connection to the database. If I quit the connection after step 3 and establish a new connection for step 4 it takes 39 seconds in either cases. -> Using one connection the optimizer for pk/fk-checking is not updated by a "vacuum analyze". On Fri, 05 Oct 2001 09:52:20 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Andreas Wernitznig <[EMAIL PROTECTED]> writes: > > If I don't disconnect from the database but use the first connection > > again, the database still uses the (now) slower "seq scan" for > > "primary/foreign key" checking. In this case the query optimizer > > statistics are not updated for established connections. > > Sure they are --- in my tests, anyway. What did you do *exactly*? > > regards, tom lane > ---(end of broadcast)--- TIP 3: 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: [BUGS] Bug #474: Index using problem
Why don't you skip the automatic index creation for primary keys and let the user decide to create an index, that should be used in any case, regardless what the query planner recommends ? On Fri, 05 Oct 2001 15:15:06 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Andreas Wernitznig <[EMAIL PROTECTED]> writes: > > -> Using one connection the optimizer for pk/fk-checking is not > > updated by a "vacuum analyze". > > Oh, I misunderstood you the first time: I thought you were saying that > *other* backends couldn't see the results of the VACUUM. > > The reason for this behavior is that the foreign key checker caches a > plan for each foreign-key-checking query the first time it needs to > use that query (within a given backend). There should be a mechanism > to flush those cached plans when circumstances change ... but currently > there isn't. > > regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #474: Index using problem
I don't know how the primary key checking and the index scan are related in postgresql (but I am keen to learn more about it). >From Sybase ASA (SQL Anywhere) I know, that these two functions (pk checking, index >search) are decoupled. (that means even a primary key without an index is possible. This makes only sense in tables with a few rows). The pg-planner takes the current number of datasets to calculate a query plan to reach the best performance. If the number of datasets changes (and the user/db-programmer usually knows if it changes) the planner sometimes make wrong decisions. Then I have to execute a "vacuum analyze" or reconnect in case of foreign key checking. I would like to tune postgresql to use an index in any case if it is available. On Fri, 05 Oct 2001 18:01:08 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Andreas Wernitznig <[EMAIL PROTECTED]> writes: > > Why don't you skip the automatic index creation for primary keys > > And how then would we enforce uniqueness of the primary key? > > > and let the user decide to create an index, that should be used in any > > case, regardless what the query planner recommends ? > > What? AFAIR the planner does not even *know* whether an index is > associated with a primary key. It certainly does not give such an > index any special preference. > > regards, tom lane > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
