Re: [GENERAL] basic question (shared buffers vs. effective cache
On Mon, 10 May 2004, Jack Orenstein wrote: scott.marlowe wrote: shared_buffers is the amount of space postgresql can use as temp memory space to put together result sets. It is not intended as a cache, and once the last backend holding open a buffer space shuts down, the information in that buffer is lost. If you're working on several large data sets in a row, the buffer currently operates FIFO when dumping old references to make room for the incoming data. Contrast this to the linux or BSD kernels, which cache everything they can in the spare memory of the computer. This cache is maintained until some other process requests enough memory to make the kernel give up some of the otherwise unused memory, or something new pushes out something old. Do checkpoints operate on the Postgres-managed buffer, or the kernel-managed cache? Checkpoints consist of writing the postgres managed data in the buffers to the drive, which is cached by the kernel, then issuing an fsync to tell the kernel to write it out to disk, so it affects both. ---(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: [GENERAL] Very slow query
Sorry for replying to my own post, but I'm anxious for an answer. Should
I provide other information?
Thanks
Rory
On 10/05/04, Rory Campbell-Lange ([EMAIL PROTECTED]) wrote:
The following query on some small datasets takes over a second to run.
I'd be grateful for some help in understanding the explain output, and
to remake the code.
Looks like the sort is using up quite a bit of resources. I made an
index on boards using columns b.n_type, b.n_id, b.t_name but the index
was not recorded in explain analyze. (see testindex below).
I am using PostgreSQL 7.4.2 on i386-pc-linux-gnu (Debian). The query is
in a psql function (I've changed variables to hard coded integers
below). The db is vacuumed every night.
Thanks for any help;
Rory
SELECT DISTINCT
b.n_id as id,
b.n_type,
CASE b.n_type WHEN 0 THEN 'personal'
WHEN 1 THEN 'private'
WHEN 2 THEN 'blog'
ELSE 'public'
ENDas type,
b.t_name as title
FROM
boards b, people p, objects o
WHERE
b.b_hidden = 'f'
AND
(
b.n_type = 3
OR
b.n_creator = 71
OR
( b.n_id = o.n_board_id
AND
o.n_creator = 71
AND
o.n_joined 0
)
)
ORDER BY
b.n_type, b.n_id ASC, b.t_name;
trial= \d boards
Table public.boards
Column |Type |Modifiers
---+-+--
n_id | integer | not null default
nextval('public.boards_n_id_seq'::text)
b_hidden | boolean | default false
dt_created| timestamp without time zone | default ('now'::text)::timestamp(6)
with time zone
dt_modified | timestamp without time zone | default ('now'::text)::timestamp(6)
with time zone
t_mobile | character varying(15) |
t_email | character varying(50) |
n_creator | integer | not null
n_type| smallint| not null default 0
t_name| character varying(100) | not null
t_description | character varying(500) |
n_id_photo| integer |
n_bg_colour | integer | default 0
Indexes:
boards_pkey primary key, btree (n_id)
boards_t_email_key unique, btree (t_email)
boards_t_mobile_key unique, btree (t_mobile)
testindex btree (n_type, n_id, t_name)
Foreign-key constraints:
$1 FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE
CASCADE
$2 FOREIGN KEY (n_id_photo) REFERENCES photo(n_id) ON UPDATE CASCADE ON DELETE
SET NULL
Triggers:
tr_update_modified_time BEFORE UPDATE ON boards FOR EACH ROW EXECUTE PROCEDURE
fn_update_modified_time()
trial= \d people
Table public.people
Column |Type |Modifiers
---+-+--
n_id | integer | not null default
nextval('public.people_n_id_seq'::text)
n_object_id | integer |
n_objects_counter | integer | default 0
b_hidden | boolean | default false
dt_created| timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
dt_modified | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
t_nickname| character varying(20) | not null
t_firstname | character varying(20) | not null
t_surname | character varying(25) | not null
t_mobile | character varying(15) |
t_email | character varying(50) |
t_password| character varying(15) | not null
b_registered | boolean | default false
n_email_status| smallint| default 0
n_txt_status | smallint| default 0
b_work_hours | boolean | default false
b_confirmations | boolean | default true
Indexes:
people_pkey primary key, btree (n_id)
people_t_email_key unique, btree (t_email)
people_t_mobile_key unique, btree (t_mobile)
people_t_nickname_key unique, btree (t_nickname)
Foreign-key constraints:
object_chk FOREIGN KEY (n_object_id) REFERENCES objects(n_id) ON UPDATE
CASCADE ON DELETE
Re: [GENERAL] nested elseif woes
On Mon, 10 May 2004 13:56:39 -0500 (EST) Kris Jurka [EMAIL PROTECTED] wrote: On Mon, 10 May 2004, Ivan Sergio Borgonovo wrote: Now let's come to the problem: create or replace function testa( ) returns char(32) as ' begin if 1=2 then if 1=2 then select 1; elseif 1=3 then select 2; elseif 1=4 then Don't you mean ELSIF, not ELSEIF? thanks to everyone. Curiously enough, trying to figure out what was wrong with my code, I've been able to write versions with the wrong spelling that didn't complain. That brought me astray. Anyway does anyone know any public big enough project written in plpgsql from which I could learn lurking at the code? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Very slow query
[snip] Rory Campbell-Lange wrote: SELECT DISTINCT b.n_id as id, b.n_type, CASE b.n_type WHEN 0 THEN 'personal' WHEN 1 THEN 'private' WHEN 2 THEN 'blog' ELSE 'public' ENDas type, b.t_name as title FROM boards b, people p, objects o WHERE b.b_hidden = 'f' AND ( b.n_type = 3 OR b.n_creator = 71 OR ( b.n_id = o.n_board_id AND o.n_creator = 71 AND o.n_joined 0 ) ) ORDER BY b.n_type, b.n_id ASC, b.t_name; You don't seem to be joining the people table to the boards or objects table. In fact the people table is never referenced anywhere but the FROM clause. This might be why it is not running so well. People seems to be referenced by both boards and objects, so does there need to be a join between all 3 tables? [snip] $1 FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE CASCADE [snip] $1 FOREIGN KEY (n_creator) REFERENCES people(n_id) ON UPDATE CASCADE ON DELETE CASCADE It is this missing join which seems to cause 158254 rows to appear in this nested loop, when PG only reckons it is going to see 2738 rows - Nested Loop (cost=3442.79..3520.93 rows=2738 width=18) (actual time=0.244..1052.180 rows=158254 loops=1) I cant remember what the correct term is, but basically all rows from the seq scan combines with all rows from the materialize, which is what is causing the 150,000 odd rows to appear (is that called a cross product anyone?) i.e 67 rows from the seq x 2362 rows from the materialize = 158254 output rows for the nested loop to chug through. - Seq Scan on people p (cost=0.00..2.67 rows=67 width=0) (actual time=0.012..0.709 rows=67 loops=1) - Materialize (cost=3442.79..3443.20 rows=41 width=18) (actual time=0.005..6.528 rows=2362 loops=67) I reckon fix the unconstrained join and your query might run a little faster. - Nested Loop (cost=3.27..3442.79 rows=41 width=18) (actual time=0.216..273.709 rows=2362 loops=1) The estimate for this nested loop seems a little off, note the guesstimate in the first set of brackets of 41, and compare with the actual result in the second set of 2362. Have you vacuum analyzed recently? HTH Nick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Interpreting vacuum verbosity
Ed L. [EMAIL PROTECTED] writes: If it were indeed the case that we'd leaked a lot of diskspace, then after bumping max_fsm_pages up to a much higher number (4M), will these pages gradually be remembered as they are accessed by autovac and or queried, etc? Or is a dump/reload or 'vacuum full' the only way? Trying to avoid downtime... The next vacuum will add the leaked space back into the FSM, once there's space there to remember it. You don't need to do anything drastic, unless you observe that the amount of wasted space is so large that a vacuum full is needed. BTW, these days, a CLUSTER is a good alternative to a VACUUM FULL; it's likely to be faster if the VACUUM would involve moving most of the live data anyway. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] nested elseif woes
On Tue, May 11, 2004 at 12:58:55AM +0200, Ivan Sergio Borgonovo wrote: thanks to everyone. Curiously enough, trying to figure out what was wrong with my code, I've been able to write versions with the wrong spelling that didn't complain. That brought me astray. Anyway does anyone know any public big enough project written in plpgsql from which I could learn lurking at the code? OpenACS (from openacs.org) is a huge web application framework with the vast majority of the business logic written in PL/SQL. It has a wide selection of PL/SQL functions, with equivalents for both Oracle and PostgreSQL. Cheers, Steve ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] vacuumdb is failing with NUMBER OF INDEX TUPLES NOT
Andrew Sullivan [EMAIL PROTECTED] writes: Dunno if this is any help, but on a 7.2 system I saw a REINDEX which was interrupted leave the index at least partially working. We ended up with an index which seemed fine, but which didn't contain certain rows (so those rows were not visible when the SELECT criterion was the indexed field). This was extremely puzzling, but a DROP INDEX; CREATE INDEX pair solved it. Hmm, I would expect that behavior for an overwrite-in-place REINDEX, but 7.2 only seems to use overwrite-in-place for critical system catalogs. What were you reindexing exactly? Were you running a standalone backend? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] query of database to get permitted users list
Hello, I have a query of pg_database to find the owners of each available database. How can I query to get the users that have permissions on any particular database? pg_user always contains *all* database users regardless of whether they have granted permissions to the current db. Thanks for any ideas. Peter ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] passing strings to functions
I can't understand how to pass strings to functions.
create type tSession
as
(
SessionCode char(32),
SessionID char(32),
UserIDI integer,
SessionN integer
);
create or replace function GetSessionCode( char(32), smallint,
boolean, varchar(128) ) returns tSession as
'
declare
newSession tSession;
...
begin
...
return newSession;
end;
' language plpgsql;
test1=# select * from
GetSessionCode('12345678901234567890123456789012',1,TRUE,'sadas');
ERROR: function getsessioncode(unknown, integer, boolean,unknown)
does not exist HINT: No function matches the given name and argument
types. You may need to add explicit type casts.
Is there a simpler way than casting everytime?
Curiously this work:
create or replace function testa( char(10) )
returns char(32) as
'
begin
return md5('''');
end;
' language plpgsql;
test1=# select * from testa('dsd');
testa
--
74b87337454200d4d33f80c4663dc5e5
(1 row)
thx
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Cancel query based on a timeout
Hi List, I'm running a query on a not-so-small db. Mostly this query runs fast enough, but every once in a while the query takes a long time to complete in wich case the users start banging away on the keyboard :-). What we would like to do is cancel the query after a certain time-out (e.g. 30 sec.) Is there some way to send a command to the postgres sever to stop running the query? We're using Postgres 7.3.4 and the latest ODBC driver. Programming is done with Borland Delphi 7 Regards, Stijn Vanroye ---(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: [GENERAL] Cancel query based on a timeout
Stijn Vanroye wrote: Hi List, I'm running a query on a not-so-small db. Mostly this query runs fast enough, but every once in a while the query takes a long time to complete in wich case the users start banging away on the keyboard :-). What we would like to do is cancel the query after a certain time-out (e.g. 30 sec.) Is there some way to send a command to the postgres sever to stop running the query? We're using Postgres 7.3.4 and the latest ODBC driver. Programming is done with Borland Delphi 7 AFAIK, there is a statement timeout option but exactly which version brought it in escapes me ATM...Most probably 7.4.x but don't take my word for it. Check you r postgrersql.conf for details. HTH Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] full tuple count in cursor.
Alexander Cohen [EMAIL PROTECTED] writes: I have a cursor that i got from a select statement. I would like to know how many records are in the cursor so i dont go over the last one when using FETCH on the cursor. How can i do this? You can't. The system does not know how many rows will be in the query until it executes it. So the only way to obtain this information is to FETCH the data. (Or MOVE over it, but that's only marginally faster, and is probably a dead loss if you intend to FETCH the data anyway later.) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] passing strings to functions
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: I can't understand how to pass strings to functions. Your problem isn't with the strings, it's with the smallint parameter. The undecorated 1 is an integer not a smallint, and it won't be silently cast in this particular scenario. Is there a simpler way than casting everytime? I'd suggest declaring the function to take integer. If you really want the rowtype to use smallint then you can cast internally. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Cancel query based on a timeout
Stijn Vanroye [EMAIL PROTECTED] writes: Is there some way to send a command to the postgres sever to stop running the query? There's a statement_timeout parameter in recent releases. I forget whether 7.3 has it though... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cancel query based on a timeout
Thanks for the reply Shridhar and Tom. But am I mistaken if setting this timeout parameter would affect all query's? That would not be the desired result. What I would like is to stop the execution of a specific (active) query if it is taking to long. If I execute the query, and it starts running, I would like to be able to abort that running query. In pgAdmin III you have a 'stop' button when you are running a query (next to the little green 'start' arrow). I would like to be able to implement something similar in my application. Regards, Stijn Vanroye -Original Message- From: Shridhar Daithankar [mailto:[EMAIL PROTECTED] Sent: maandag 10 mei 2004 13:50 To: Stijn Vanroye Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Cancel query based on a timeout Stijn Vanroye wrote: Hi List, I'm running a query on a not-so-small db. Mostly this query runs fast enough, but every once in a while the query takes a long time to complete in wich case the users start banging away on the keyboard :-). What we would like to do is cancel the query after a certain time-out (e.g. 30 sec.) Is there some way to send a command to the postgres sever to stop running the query? We're using Postgres 7.3.4 and the latest ODBC driver. Programming is done with Borland Delphi 7 AFAIK, there is a statement timeout option but exactly which version brought it in escapes me ATM...Most probably 7.4.x but don't take my word for it. Check you r postgrersql.conf for details. HTH Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Cancel query based on a timeout
Stijn Vanroye wrote: Thanks for the reply Shridhar and Tom. But am I mistaken if setting this timeout parameter would affect all query's? That would not be the desired result. What I would like is to stop the execution of a specific (active) query if it is taking to long. If I execute the query, and it starts running, I would like to be able to abort that running query. In pgAdmin III you have a 'stop' button when you are running a query (next to the little green 'start' arrow). I would like to be able to implement something similar in my application. You could put the database connection in asynchronous mode and use PQrequestCancel. Please check http://www.postgresql.org/docs/7.3/static/libpq-async.html. This is native C interface though. I have no idea to make it work with delphi and/or ODBC. HTH Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
