Re: [PERFORM] General performance problem!
An index
on cp and effectif would help your first query. An index on naf, cp and effectif would help your second
query.
Something
like this:
CREATE INDEX base_aveugle_cp_key2 ON
base_aveugle USING btree (cp, effectif);
CREATE INDEX base_aveugle_naf_key2 ON base_aveugle USING btree (naf, cp,
effectif);
Another thing, why include “distinct cp”
when you are only selecting “cp=’201A’”?
You will only retrieve one record regardless of how many may contain cp=’201A’.
If you could make these UNIQUE indexes that
would help also but it’s not a requirement.
Good luck,
Duane
-Original
Message-
From: olivier HARO
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 17, 2004
6:30 AM
To:
[EMAIL PROTECTED]
Subject: [PERFORM] General
performance problem!
Hello,
I
have a dedicated server for my posgresql database :
P4
2.4 GHZ
HDD
IDE 7200 rpm
512
DDR 2700
I
have a problem whith one table of my database :
CREATE
SEQUENCE "base_aveugle_seq" START 1;
CREATE TABLE "base_aveugle" (
"record_id" integer DEFAULT
nextval('"base_aveugle_seq"'::text) NOT NULL,
"dunsnumber" integer NOT NULL,
"cp" text NOT NULL,
"tel" text NOT NULL,
"fax" text NOT NULL,
"naf" text NOT NULL,
"siege/ets" text NOT NULL,
"effectif" integer NOT NULL,
"ca" integer NOT NULL,
Constraint "base_aveugle_pkey" Primary Key
("record_id")
);
CREATE INDEX base_aveugle_dunsnumber_key ON base_aveugle USING btree
(dunsnumber);
CREATE INDEX base_aveugle_cp_key ON base_aveugle USING btree (cp);
CREATE INDEX base_aveugle_naf_key ON base_aveugle USING btree (naf);
CREATE INDEX base_aveugle_effectif_key ON base_aveugle USING btree (effectif);
This
table contains 5 000 000 records
I
have a PHP application which often makes queries on this table (especially on
the "cp","naf","effectif" fields)
Querries
are like :
select (distint cp) from base_aveugle where cp='201A' and effectif between 1
and 150
select (*) from base_aveugle where naf in ('721A','213F','421K') and cp in
('54210','21459','201A') and effectif < 150
I
think it is possible to optimize the performance of this queries before
changing the hardware (I now I will...) but I don't know how, even after
having read lot of things about postgresql ...
Thanks
;)
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11/08/2004
Re: [PERFORM] postgres 7.4 at 100%
Title: RE: [PERFORM] postgres 7.4 at 100% Creating indexes on a table affects insert performance depending on the number of indexes that have to be populated. From a query standpoint, indexes are a godsend in most cases. Duane -Original Message- From: Chris Cheston [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 30, 2004 12:19 AM To: Gavin M. Roy Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] postgres 7.4 at 100% Oh my, creating an index has absolutely reduced the times it takes to query from around 700 ms to less than 1 ms! Thanks so much for all your help. You've saved me! One question: Why would I or would I not create multiple indexes in a table? I created another index in the same table an it's improved performance even more. Thanks, Chris On Tue, 29 Jun 2004 09:03:24 -0700, Gavin M. Roy <[EMAIL PROTECTED]> wrote: > > Is the from field nullable? If not, try "create index calllogs_from on > calllogs ( from );" and then do an explain analyze of your query. > > Gavin > > > > Chris Cheston wrote: > > >ok i just vacuumed it and it's taking slightly longer now to execute > >(only about 8 ms longer, to around 701 ms). > > > >Not using indexes for calllogs(from)... should I? The values for > >calllogs(from) are not unique (sorry if I'm misunderstanding your > >point). > > > >Thanks, > > > >Chris > > > >On Tue, 29 Jun 2004 16:21:01 +0800, Christopher Kings-Lynne > ><[EMAIL PROTECTED]> wrote: > > > > > >>>live=# explain analyze SELECT id FROM calllogs WHERE from = 'you'; > >>> QUERY PLAN > >>>-- > >>> Seq Scan on calllogs (cost=0.00..136.11 rows=24 width=4) (actual > >>>time=0.30..574.72 rows=143485 loops=1) > >>> Filter: (from = 'you'::character varying) > >>> Total runtime: 676.24 msec > >>>(3 rows) > >>> > >>> > >>Have you got an index on calllogs(from)? > >> > >>Have you vacuumed and analyzed that table recently? > >> > >>Chris > >> > >> > >> > >> > > > >---(end of broadcast)--- > >TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] is it possible to for the planner to optimize this
Title: RE: [PERFORM] is it possible to for the planner to optimize this form? I didn't really look that closely at the problem but have you thought of trying: select t.key, t.field from t a , (select count(*) as cntb from t b where b.field > a.field) as dmytbl where cntb = k This is called an inline view or sometimes a nested table. You would be joining table t to this inline view with the join criteria being "cntb = k" where k is in t. -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED]] Sent: Monday, June 07, 2004 1:32 PM To: Merlin Moncure; [EMAIL PROTECTED] Subject: Re: [PERFORM] is it possible to for the planner to optimize this form? Merlin, > select t.key, t.field from t a > where > ( > select count(*) from t b > where b.field > a.field > ) = k > > The subplan (either index or seq. scan) executes once for each row in t, > which of course takes forever. > > This query is a way of achieving LIMIT type results (substitute n-1 > desired rows for k) using standard SQL, which is desirable in some > circumstances. Is it theoretically possible for this to be optimized? I don't think so, no. PostgreSQL does have some issues using indexes for count() queires which makes the situation worse. However, with the query you presented, I don't see any way around the planner executing the subquery once for every row in t. Except, of course, for some kind of scheme involving materialized views, if you don't need up-to-the minute data. In that case, you could store in a table the count(*)s of t for each threshold value of b.field. But, dynamically, that would be even slower. -- Josh Berkus Aglio Database Solutions San Francisco ---(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: [PERFORM] Trigger & Function
Title: RE: [PERFORM] Trigger & Function Thanks for the response. I was pretty sure it couldn't be done the way I wanted to but felt I would ask anyway. Thanks again, Duane -Original Message- From: Mike Nolan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 01, 2004 3:04 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PERFORM] Trigger & Function > My problem is I defined the "before" and "after" > fields in the audit table as TEXT and when I try to move NEW or OLD into > these fields I get the error "NEW used in query that is not in a rule". You're trying to insert record data into a text field, that doesn't work. OLD and NEW can be used as either record identifiers (as in RETURN OLD) or column qualifiers (as in OLD.colname), but you can't intermingle them. I don't think postgres (pl/pgsql) has row-to-variable and variable-to-row functions like serialize and unserialize, that's probably what you'd need. It would probably be necessary to write something like that in C, since at this point pl/perl cannot be used for trigger functions. I've not tried using pl/php yet, the announcement for it says it can be used for trigger functions. My first thought is that even if there was a serialize/unserialize capabiity you might be able to write something using it that creates the log entry but not anything that allows you to query the log for specific column or row entries. It would probably require a MAJOR extension of SQL to add it to pg, as there would need to be qualifiers that can be mapped to specific tables and columns. Even if we had that, storing values coming from multiple tables into a single audit table would present huge challenges. I've found only two ways to implement audit logs: 1. Have separate log tables that match the structure of the tables they are logging. 2. Write a trigger function that converts columns to something you can store in a common log table. (I've not found a way to do this without inserting one row for each column being logged, though.) -- Mike Nolan
[PERFORM] Trigger & Function
Title: Trigger & Function I'm trying to create a trigger (AFTER INSERT, UPDATE, DELETE) as an audit routine inserting into an audit table the "before" and "after" views of the row being acted upon. My problem is I defined the "before" and "after" fields in the audit table as TEXT and when I try to move NEW or OLD into these fields I get the error "NEW used in query that is not in a rule". I tried defining a variable as RECORD type but when I tried executing I would get a "syntax error at or near..." the variable when it was referenced in the code (new_fld := NEW for instance). I'm currently stumped. I don't want an audit table for each and every table I want to audit. I want a single audit table to handle multiple tables. Do any of you more astute users have any ideas to help me? Can you tell me where I'm going wrong? Is my wish to have a single audit table for multiple tables all folly? An explanation of the "rule" error shown above would help as well. Any help will be appreciated. TIA, Duane Here is the function definition: CREATE OR REPLACE FUNCTION func_aud_tst01() RETURNS trigger AS ' DECLARE action char(1); b4 text; aftr text; BEGIN IF TG_OP = ''INSERT'' THEN action := ''I''; b4 := ; aftr := NEW; -- b4 := ''Test b4 I''; -- aftr := ''Test aftr I''; ELSIF TG_OP = ''UPDATE'' THEN action := ''U''; -- b4 := OLD; -- aftr := NEW; b4 := ''Test b4 U''; aftr := ''Test aftr U''; ELSE action := ''D''; -- b4 := OLD; -- aftr := ; b4 := ''Test b4 D''; aftr := ''Test aftr D''; END IF; insert into audtst(table_name, act_type, before_look, after_look) values(TG_RELNAME, action, b4, aftr); RETURN NEW; END; ' LANGUAGE plpgsql; -- COMMIT WORK;
[PERFORM] Hardware Platform
Title: Hardware Platform I'm working on a project using PostgreSQL as our database designing a budget system. We are still in the design and testing phases but I thought I would ask advice about a platform to host this system. We aren't a large system, probably no more than 50-75 users at any one time. From a data standpoint I can't guess at the number of gigabytes but suffice to say it is not going to be that large. Our biggest table will probably hold about 1 million rows and is about 120 bytes (closer to about 100). Dell and HP servers are being mentioned but we currently have no preference. Any help you could provide will be appreciated. Thanks, Duane P.S. I've only just begun using PostgreSQL after having used (and still using) DB2 on a mainframe for the past 14 years. My experience with Unix/Linux is limited to some community college classes I've taken but we do have a couple of experienced Linux sysadmins on our team. I tell you this because my "ignorance" will probably show more than once in my inquiries.
