[BUGS] BUG #4819: Ordering big tables by indexed columns is very slow.
The following bug has been logged online: Bug reference: 4819 Logged by: Alexei Email address: alexei_nova...@yahoo.com PostgreSQL version: 8.3.7 Operating system: Open SuSE 11.1 AMD Athlon 64 X2 Description:Ordering big tables by indexed columns is very slow. Details: Hello. I have very simple query, which runs very long when has "order by" clause, even though all columns in "order by" are indexed. Here is the simplified testcase. 1) Table: create table tmp1 ( field1 bigint not null, field2 integer not null ) 2) Data: I generated some test data for this table: field2 is always 2; field1 starts from 1242865824484 and every next one is incremented by 1. I generated 3 million records. 3) Index: create index tmp1_idx on tmp1 (field1, field2) 4) Query: select field1, field2 from tmp1 order by 1, 2 The query plan for this query is: Sort (cost=522779.47..530279.47 rows=300 width=12) Sort Key: field1, field2 -> Seq Scan on tmp1 (cost=0.00..46217.00 rows=300 width=12) Index is not used for the sorting here. But if I add "limit 1000" in the end I get the following: Limit (cost=0.00..75.33 rows=100 width=12) -> Index Scan using tmp1_idx on tmp1 (cost=0.00..2259857.96 rows=300 width=12) If I increase limit to 70 index is not used again and the difference in execution time is very noticeable: 1 millisecond for "limit 60"; and 6 seconds for "limit 70" Is there anything what can be configured to make it use the index for the ordering? Best Regards, Alexei Novakov. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #1878: Different execution plans for the same query.
The following bug has been logged online: Bug reference: 1878 Logged by: Alexei Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: Windows 2000 Description:Different execution plans for the same query. Details: I was doing performance evaluation of various DB servers including PostgreSQL. For one of test queries I got strange result - timing was different (from 0.5 to 240 sec) even if I was running it one time after another without going anything in between (like there were no inserts or whatever similar). I did vacuum and vacuum analyse, but it didn't change anything. I checked query plan and found that this plan is different every time. Sometimes it generates plan which uses indices all the way, but sometimes it goes for table (or even two tables) scan. Although the result of the query always was correct whichever plan it's using. I repete - I did not do anything between queries, but just ran the same 'explain select ...' sequentially. The query itself is heavy, but pretty strightforward - 2 tables joined 12 times. Here it is, just in case: select T1.MAIN_ID, T2.ORDER_F, T3.ORDER_F, T4.ORDER_F, T5.ORDER_F, T6.LEVEL_IND, T6.PRE_IND, T7.ORDER_F, T8.ORDER_F, T9.ORDER_F, T10.ORDER_F, T11.ORDER_F, T12.MAIN_ID from TMP_MAIN T1, TMP_RS T2, TMP_RS T3, TMP_RS T4, TMP_RS T5, RSCACHE_R T6, TMP_RS T7, TMP_RS T8, TMP_RS T9, TMP_RS T10, TMP_RS T11, TMP_MAIN T12 where T2.ID_2 = T1.MAIN_ID and T2.ID_TYPE = 269 and 990765472621084681 = T2.ID_1 and T4.ID_1 = T12.MAIN_ID and T4.ID_TYPE = 143 and T3.ID_TYPE = 143 and 990765472621084681 = T3.ID_1 and T4.ID_2 = T3.ID_2 and T7.ID_1 = T12.MAIN_ID and T7.ID_TYPE = 224 and T6.ID_TYPE = 209 and T6.LEVEL_IND != 0 and T5.ID_TYPE = 224 and 990765472621084681 = T5.ID_1 and T6.ID_1 = T5.ID_2 and T7.ID_2 = T6.ID_2 and T9.ID_1 = T12.MAIN_ID and T9.ID_TYPE = 125 and T8.ID_TYPE = 125 and 990765472621084681 = T8.ID_1 and T9.ID_2 = T8.ID_2 and T11.ID_1 = T12.MAIN_ID and T11.ID_TYPE = 71 and T10.ID_TYPE = 71 and 990765472621084681 = T10.ID_1 and T11.ID_2 = T10.ID_2 and T12.ID_TYPE = 286 Both tables have 50-100 records. If you need more information contact me via e-mail. Regards. Alexei. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2110: initdb set wrong default encoding
The following bug has been logged online:
Bug reference: 2110
Logged by: Alexei
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.1
Operating system: Window XP SP2
Description:initdb set wrong default encoding
Details:
Good day!
I run initdb with --encoding=WIN1251 parameter, but
then 'psql -l' show that encoding of 'template{0,1}'
and 'postgres' databases is KOI8-R.
New databases created with KOI8-R encoding also,
unless specified by 'ENCODING' keyword, or '--encoding'
parameter.
During run of initdb no warning or error messages
printed.
It seems, that actually the encoding of 'template{0,1}' and 'postgres'
databases is valid (CP1251):
query "select 'â'" print 'â'. (â - is a symbol
present in CP1251 at code 0x00b9 and not present in KOI8-R)
But in newly created database the same query print empty space (as in KOI8-R
database), and 'psql -l' show, that ALL databases have KOI8-R encoding.
Thank you and sorry for my English.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[BUGS]
Your name : Alexei A. Romanenko
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.12 ELF
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0) :
Please enter a FULL description of your problem:
It seems to me there is a problem with regular expressions.
When i create table and try to insert some restriction for
a fields, system accept it. Then, whem i insert something, which
dont match to regexp, it inserted anyway or backwards.
Maybe i am do something wrong, but...
Please describe a way to repeat the problem. Please try to
provide a concise reproducible example, if at all possible:
--
arom> psql
1.
arom=> create table vv (v char(10) CHECK (v ~*
'^[a-z]+\@[a-z]+'));
CREATE
arom=> INSERT INTO vv VALUES ('aq@');
INSERT 19863 1
arom=> INSERT INTO vv VALUES (' aq@');
ERROR: ExecAppend: rejected due to CHECK constraint vv_v
arom=> INSERT INTO vv VALUES ('aq@2');
ERROR: ExecAppend: rejected due to CHECK constraint vv_v
arom=> INSERT INTO vv VALUES ('aq@');
INSERT 19864 1
arom=> drop table vv;
DROP
arom=> create table vv (v char(10) CHECK (v ~* '^[a-z]+$'));
CREATE
arom=> INSERT INTO vv VALUES ('aq');
ERROR: ExecAppend: rejected due to CHECK constraint vv_v
In POSIX standart symbol '$' means END OF LINE. Right?
2.
arom=> create table vv (v char(10) CHECK (v ~* '[:alnum:]+'));
CREATE
arom=> INSERT INTO vv VALUES ('12345');
ERROR: ExecAppend: rejected due to CHECK constraint vv_v
^
arom=> INSERT INTO vv VALUES ('abcd');
INSERT 19895 1
arom=> INSERT INTO vv VALUES ('123abcd');
INSERT 19896 1
arom=> drop table vv;
DROP
[:alnum:] means [:alpha:] and [:digit:].
But [a-z0-9] works correct
If you know how this problem might be fixed, list the solution
below:
-
[BUGS] UPDATE bug in PostgreSQL-7.1.3
Hi! I have found that some of my queries stop working after upgrade from 7.0 to 7.1.3. Some investigation show that UPDATE queries grow to crash. Here is my SQL code that produces error (it is supposed to be in pg_bug.sql file): -- \connect template1 postgres CREATE DATABASE "pg_bug" WITH TEMPLATE = template0; \connect pg_bug postgres CREATE TABLE "buggy" ( "field1" integer UNIQUE ); GRANT ALL on "buggy" to "postgres"; COPY "buggy" FROM stdin; 1 2 3 \. UPDATE buggy SET field1 = field1+1; -- And here is the execution result: bash$ /usr/local/pgsql/bin/psql -d template1 -f ~/tmp/pg_bug.sql You are now connected to database template1 as user postgres. CREATE DATABASE You are now connected to database pg_bug as user postgres. psql:/home/barancev/tmp/pg_bug.sql:7: NOTICE: CREATE TABLE/UNIQUE will create implicit index 'buggy_field1_key' for tab le 'buggy' CREATE CHANGE psql:/home/barancev/tmp/pg_bug.sql:17: ERROR: Cannot insert a duplicate key into unique index buggy_field1_key bash$ So you can see UPDATE failed. Best regards, Alexei -- Alexei Barantsev, ISP RAS E-mail: [EMAIL PROTECTED] ICQ : 3959207 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] BUG #4081: Running Postgres as Windows service vs regular applications
The following bug has been logged online: Bug reference: 4081 Logged by: Pavel Alexei Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.7 Operating system: Windows XP SP2 Description:Running Postgres as Windows service vs regular applications Details: I faced with problem connecting to Postgres running as Windows service. After installing I tried to connect using pgAdmin, but Iâve got errors regarding troubles in connecting and found next errors in logfile: >connection received: host=127.0.0.1 port=13683 >could not receive data from client: An operation was attempted on something that is not a socket. >incomplete startup packet I found through Google a lot of messages, several people had same problem. But I didnât find solution. The only advice was to launch Postgres as simple application (pg_ctl.exe start instead runservice). It was worked for me too. But I decided to get understood problem. I tried to get more details and raised all logging parameters in configuration file to debug5 (âclient_min_messagesâ, âlog_min_messagesâ, âlog_min_error_statementâ), but after this errors disappeared. I turned back all parameters and got same errors. After several experiments I got ârightâ target. If I put âlog_min_messagesâ to debug2 or higher (2-5) everything is ok. But is enough to set this parameter to debug1 or lower and Iâve got same problem. Very strange thing to be coincidence. Logfile with log_min_messages = debug1 2008-04-01 16:42:24 LOG: database system was shut down at 2008-04-01 16:42:23 2008-04-01 16:42:24 LOG: checkpoint record is at 0/173E990 2008-04-01 16:42:24 LOG: redo record is at 0/173E990; undo record is at 0/0; shutdown TRUE 2008-04-01 16:42:24 LOG: next transaction ID: 0/24871; next OID: 25829 2008-04-01 16:42:24 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-04-01 16:42:24 LOG: database system is ready 2008-04-01 16:42:24 DEBUG: transaction ID wrap limit is 2147484171, limited by database "postgres" 2008-04-01 16:42:33 [unknown] [unknown] LOG: connection received: host=127.0.0.1 port=13682 2008-04-01 16:42:33 postgres lua authenticationLOG: connection authorized: user=lua database=postgres 2008-04-01 16:42:33 [unknown] [unknown] LOG: connection received: host=127.0.0.1 port=13683 2008-04-01 16:42:33 [unknown] [unknown] LOG: could not receive data from client: An operation was attempted on something that is not a socket. 2008-04-01 16:42:33 [unknown] [unknown] LOG: incomplete startup packet 2008-04-01 16:42:35 [unknown] [unknown] LOG: connection received: host=127.0.0.1 port=13684 2008-04-01 16:42:35 lua lua authenticationLOG: connection authorized: user=lua database=lua 2008-04-01 16:42:50 LOG: received fast shutdown request 2008-04-01 16:42:50 LOG: aborting any active transactions 2008-04-01 16:42:50 lua lua idleFATAL: terminating connection due to administrator command 2008-04-01 16:42:50 postgres lua idleFATAL: terminating connection due to administrator command 2008-04-01 16:42:50 postgres lua idleLOG: disconnection: session time: 0:00:17.081 user=lua database=postgres host=127.0.0.1 port=13682 2008-04-01 16:42:50 lua lua idleLOG: disconnection: session time: 0:00:15.440 user=lua database=lua host=127.0.0.1 port=13684 2008-04-01 16:42:50 LOG: shutting down 2008-04-01 16:42:50 LOG: database system is shut down 2008-04-01 16:42:51 LOG: logger shutting down Logfile with log_min_messages = debug2 2008-04-01 16:42:52 LOG: database system was shut down at 2008-04-01 16:42:50 2008-04-01 16:42:52 LOG: checkpoint record is at 0/173E9E0 2008-04-01 16:42:52 LOG: redo record is at 0/173E9E0; undo record is at 0/0; shutdown TRUE 2008-04-01 16:42:52 LOG: next transaction ID: 0/24910; next OID: 25829 2008-04-01 16:42:52 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-04-01 16:42:52 LOG: database system is ready 2008-04-01 16:42:52 DEBUG: transaction ID wrap limit is 2147484171, limited by database "postgres" 2008-04-01 16:43:11 DEBUG: forked new backend, pid=6856 socket=1368 2008-04-01 16:43:12 [unknown] [unknown] LOG: connection received: host=127.0.0.1 port=13686 2008-04-01 16:43:12 postgres lua authenticationLOG: connection authorized: user=lua database=postgres 2008-04-01 16:43:12 DEBUG: forked new backend, pid=4464 socket=1380 2008-04-01 16:43:12 [unknown] [unknown] LOG: connection received: host=127.0.0.1 port=13687 2008-04-01 16:43:12 lua lua authenticationLOG: connection authorized: user=lua database=lua -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] bugs
POSTGRESQL BUG REPORT TEMPLATE
Your name : Alexei E. Korneyev
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture : Intel Pentium II
Operating System : Linux RedHat 2.2.12
PostgreSQL version : PostgreSQL-7.0.2
Compiler used : uncnown Build Host: utility.wgcr.org
Please enter a FULL description of your problem:
i create this:
create table "users" (
namevarchar(10) PRIMARY KEY
);
create table "stat" (
peernamevarchar(10) REFERENCES users(name)
);
revoke ALL on stat,users from PUBLIC;
grant INSERT, SELECT on stat to statist;
grant select on users to statist;
grant ALL on stat to basemaster;
insert into users values('niva');
insert into stat values ('niva',abstime('now'),timespan(0),8607272,1829436);
-
After that I do(make) connection as the user statis and execute cmd:
---
insert into stat
values('niva',abstime('now'),timespan(abstime('now')-(select max(timewrite)
from stat)),50,50);";
--
server say:Access deny on users...
After I change the rights on:
grant select,update on users to statist;
,the query exec successfully
Alexei E. Korneyev
[EMAIL PROTECTED]
Re: [BUGS] Possible bug in referential integrity system
Hello! Keys phrase 'NOT NULL' simple=# create table t1 (num int4 PRIMARY KEY, name text); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 't1_pkey' for table 't1' CREATE simple=# create table t2 (ref int4 references t1 (num) NOT NULL, val text); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE simple=# insert into t1 values (1, 'Widget1'); INSERT 80324 1 simple=# insert into t2 values ( (select num from t1 where name = 'widget2'), 'Bug?'); ERROR: ExecAppend: Fail to add null value in not null attribute ref > => create table t1 (num int4, name text); > CREATE > => create table t2 (ref int4 references t1 (num), val text); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > > => insert into t1 values (1, 'Widget1'); > INSERT 17518650 1 > > => insert into t2 values ( (select num from t1 where name = 'Widget1'), 'Valuable'); > INSERT 17518651 1 > => insert into t2 values ( (select num from t1 where name = 'widget2'), 'Bug?'); > INSERT 17518652 1 Alexei E. Korneyev [EMAIL PROTECTED]
