Re: [SQL] join on three tables is slow
Tom Lane wrote:
Gerry Reno <[EMAIL PROTECTED]> writes:
Pavel Stehule wrote:
there is diference in agg position. Send, please, query and explain
analyze output.
[ explain analyze output ]
The rowcount estimates seem pretty far off, even for simple cases that
I'd expect it to get right, eg
-> Seq Scan on res_partner_address a (cost=0.00..88.40
rows=16 width=552) (actual time=0.851..16.131 rows=559 loops=1)
Filter: ((("type")::text = 'default'::text) OR ("type" IS
NULL))
Are the ANALYZE stats up to date for these tables?
What PG version is this, anyway?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
-bash-3.2$ yum list postgresql
Loading "installonlyn" plugin
Installed Packages
postgresql.i386 8.2.4-1.fc7
installed
You were right on the money Tom. I vacuumed the database and now the
query with the boolean executes in only 50% more time than without.
About 15 secs instead of 10 secs. Big improvement. I hadn't seen a
vacuum produce this much of an improvement in performance before.
Thanks,
Gerry
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query design assistance - getting daily totals
On Dec 12, 2007 12:39 AM, Paul Lambert <[EMAIL PROTECTED]> wrote: > A. Kretschmer wrote: > > am Wed, dem 12.12.2007, um 10:34:35 +0900 mailte Paul Lambert folgendes: > >> year_id integer > >> month_id integer > >> working_day integer > > > > Why this broken data types? We have date and timestamp[tz]. > > > > > > It's a financial application which needs to work using a concept of > 'financial periods' which may not necessarily correspond to calendar > months and it's much easier to manage in this way than it is to merge it > all together using a date field. Eg, 1st January may actually be the > 15th 'working day' of the 9th 'financial period' - however looking at > just a date of jan-1 there is no way of knowing this and it's the > periods that matter more so than the actual date. I'm not sure that really justifies your method though. Not saying "you're doing it wrong" so much as I'm not sure the way you're doing it makes it any easier to keep track of certain periods. Any method you would use to pick rows with the disjointed dates could be applied to date and / or timestamp types as easily, and with some functional indexes on the date / timestamp columns you could easily select periods quickly as well. Just saying. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Query Assistance
All... I have a simple table in PostgreSQL 8.2.5: CREATE TABLE power_transitions ( -- Transition ID (PK) tid integer NOT NULL, -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery) sid smallint NOT NULL, -- Timestamp of transition statetime timestamp without time zone DEFAULT now() NOT NULL, -- Is this a real outage? is_outage boolean DEFAULT true NOT NULL ); It contains a log of power outages (transitions). I'd like to create query that returns a transition offline time and associated return to online time. Is there a better way of handling this? I am open to schema change suggestions. Thanks very much! -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query Assistance
D'Arcy... > Have you considered this? I considered integrating the begin and end times into the table. I'm capturing the data via ACPI events, so it's "transactional" by nature. I want to be able to keep track of false transitions (hence the is_outage field). I'm looking for a way to simplify the transitions output on my web browser, and I want to combine an offline and a corresponding online time into a single line. > I am also thinking of a scheme that uses two tables but I don't really > know your environment or requirements. I am assuming that you spend > more time querying the table than updating it. If not your problem > isn't your database, it's your power plant. :-) It's nothing complex at all. The power in my neighborhood is infamously unstable, and I'm merely keep records to complain as much as I can to the power company. Thanks for taking the time to reply! -- Gary Chambers // Nothing fancy and nothing Microsoft! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Query Assistance
On Wed, 12 Dec 2007 11:58:20 -0500 "Gary Chambers" <[EMAIL PROTECTED]> wrote: > All... > > I have a simple table in PostgreSQL 8.2.5: > > CREATE TABLE power_transitions ( > -- Transition ID (PK) > tid integer NOT NULL, > -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery) > sid smallint NOT NULL, > -- Timestamp of transition > statetime timestamp without time zone DEFAULT now() NOT NULL, > -- Is this a real outage? > is_outage boolean DEFAULT true NOT NULL > ); > > It contains a log of power outages (transitions). I'd like to create > query that returns a transition offline time and associated return to > online time. Is there a better way of handling this? I am open to > schema change suggestions. Thanks very much! Have you considered this? CREATE TABLE power_transitions ( -- Transition ID (PK) tid integer NOT NULL, -- Timestamp of power off ( starttime timestamp without time zone DEFAULT now() NOT NULL, -- Timestamp of power on ( endtime timestamp without time zone, -- Is this a real outage? may not be needed. is_outage boolean DEFAULT true NOT NULL ); The is_outage bool could be handled with special timestamps (e.g. EPOCH) but I am not sure what it signifies to you. Of course you have to deal with false transitions but I don't know what your information capture system is so I can't work out all the details but this seems like a good base for what you want. Every row that has a valid start and end time is a complete record of an outage. I am also thinking of a scheme that uses two tables but I don't really know your environment or requirements. I am assuming that you spend more time querying the table than updating it. If not your problem isn't your database, it's your power plant. :-) -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Query design assistance - getting daily totals
On Dec 12, 2007 1:39 AM, Paul Lambert <[EMAIL PROTECTED]> wrote: > It's a financial application which needs to work using a concept of > 'financial periods' which may not necessarily correspond to calendar > months and it's much easier to manage in this way than it is to merge it > all together using a date field. Eg, 1st January may actually be the > 15th 'working day' of the 9th 'financial period' - however looking at > just a date of jan-1 there is no way of knowing this and it's the > periods that matter more so than the actual date. I think what you need is a Calendar Table to "map" actual dates to "buckets" e.g. 'financial periods', etc. See: http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query Assistance
Gary Chambers wrote: D'Arcy... Have you considered this? I considered integrating the begin and end times into the table. I'm capturing the data via ACPI events, so it's "transactional" by nature. I want to be able to keep track of false transitions (hence the is_outage field). I'm looking for a way to simplify the transitions output on my web browser, and I want to combine an offline and a corresponding online time into a single line. How about a "paired_with" field that references the power_transitions table and a trigger. When you insert a new row, it checks what the previous row was - if it's a down & this is an up, then set the paired_with field on each. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Trigger definition . . . puzzled
Greetings list, Running pg 8.2.3. on a windows machine, I have become blind in a trigger definition puzzle, so hope that somebody may help me understand where I goof. I have a base table i_s that has three tables that inherit from it, one of them being i_s_nowhere. The base table should be left empty, but I want it to be the prime port of call for data changes. Hence the following set-up. Let me try to be brief, in the hope of not leaving out relevant detail. Base table def is as follows: CREATE TABLE i_s ( sidx integer NOT NULL, -- The s identifier gid integer NOT NULL, -- The i identifier status character(1), confirmation character(1), CONSTRAINT pk_is PRIMARY KEY (sidx, gid) ) WITH (OIDS=FALSE); And the trigger definition is here: CREATE TRIGGER aw_archival_is2 BEFORE UPDATE OR INSERT OR DELETE ON i_s FOR EACH ROW EXECUTE PROCEDURE aw_archive_test(); And the trigger function: CREATE OR REPLACE FUNCTION aw_archive_test() RETURNS "trigger" AS $BODY$ BEGIN RAISE WARNING 'Starting isa trigger for %', TG_OP; IF (TG_OP = 'UPDATE') THEN RAISE WARNING 'Calling insert_isa with update'; ELSIF (TG_OP = 'INSERT') THEN RAISE WARNING 'Calling insert_isa with insert'; ELSIF (TG_OP = 'DELETE') THEN RAISE WARNING 'Calling insert_isa with delete'; END IF;RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The trigger is enabled. Yes, this does intentionally nothing. The real code will obviously take care of data change in proper subtables. Well, the trigger should do nothing now . . . What I cannot get round to understanding is that an insert attempt will nicely give me two warnings, and will not insert, as expected with this code: WARNING: Starting isa trigger for INSERT WARNING: Calling insert_isa with insert Query returned successfully: 0 rows affected, 31 ms execution time. But an attempt to update actually magically goes to the proper subtable and performs the update: Query returned successfully: 1 rows affected, 16 ms execution time. Where did I deserve this?? ;-) In attempts to solve this I did mess around with trigger and trigger function definitions a bit. Could there be funny traces of this? What is the best way to analyse this behavior? I am testing from a pgAdmin 1.8 setup. All suggestions welcome! -- Rolf A. de By The Netherlands ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Trigger definition . . . puzzled
On Dec 12, 2007, at 3:38 PM, Rolf A. de By wrote: Greetings list, Running pg 8.2.3. on a windows machine, I have become blind in a trigger definition puzzle, so hope that somebody may help me understand where I goof. I have a base table i_s that has three tables that inherit from it, one of them being i_s_nowhere. The base table should be left empty, but I want it to be the prime port of call for data changes. Hence the following set-up. Let me try to be brief, in the hope of not leaving out relevant detail. Base table def is as follows: CREATE TABLE i_s ( sidx integer NOT NULL, -- The s identifier gid integer NOT NULL, -- The i identifier status character(1), confirmation character(1), CONSTRAINT pk_is PRIMARY KEY (sidx, gid) ) WITH (OIDS=FALSE); And the trigger definition is here: CREATE TRIGGER aw_archival_is2 BEFORE UPDATE OR INSERT OR DELETE ON i_s FOR EACH ROW EXECUTE PROCEDURE aw_archive_test(); And the trigger function: CREATE OR REPLACE FUNCTION aw_archive_test() RETURNS "trigger" AS $BODY$ BEGIN RAISE WARNING 'Starting isa trigger for %', TG_OP; IF (TG_OP = 'UPDATE') THEN RAISE WARNING 'Calling insert_isa with update'; ELSIF (TG_OP = 'INSERT') THEN RAISE WARNING 'Calling insert_isa with insert'; ELSIF (TG_OP = 'DELETE') THEN RAISE WARNING 'Calling insert_isa with delete'; END IF;RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The trigger is enabled. Yes, this does intentionally nothing. The real code will obviously take care of data change in proper subtables. Well, the trigger should do nothing now . . . What I cannot get round to understanding is that an insert attempt will nicely give me two warnings, and will not insert, as expected with this code: WARNING: Starting isa trigger for INSERT WARNING: Calling insert_isa with insert Query returned successfully: 0 rows affected, 31 ms execution time. But an attempt to update actually magically goes to the proper subtable and performs the update: Query returned successfully: 1 rows affected, 16 ms execution time. Where did I deserve this?? ;-) In attempts to solve this I did mess around with trigger and trigger function definitions a bit. Could there be funny traces of this? What is the best way to analyse this behavior? I am testing from a pgAdmin 1.8 setup. The function you've shown won't do anything because BEFORE row triggers that return NULL don't do anything (for that row). If you want the operation to continue without any modification then just return NEW. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(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: [SQL] Trigger definition . . . puzzled
Erik, Thanks for that. There is some misunderstanding here. For this example, I had taken the sting out of my trigger function and turned it into a much more concise no-op, with warnings. The actual code of my original trigger function is irrelevant. The no-op trigger function displays the same strange behaviour: it works as expected for INSERTs, but not for UPDATEs. The update goes through! And it shouldn't. My question to the list is how I can analyze what is happening here, I am lost at where to start on that. Rolf Greetings list, Running pg 8.2.3. on a windows machine, I have become blind in a trigger definition puzzle, so hope that somebody may help me understand where I goof. I have a base table i_s that has three tables that inherit from it, one of them being i_s_nowhere. The base table should be left empty, but I want it to be the prime port of call for data changes. Hence the following set-up. Let me try to be brief, in the hope of not leaving out relevant detail. Base table def is as follows: CREATE TABLE i_s ( sidx integer NOT NULL, -- The s identifier gid integer NOT NULL, -- The i identifier status character(1), confirmation character(1), CONSTRAINT pk_is PRIMARY KEY (sidx, gid) ) WITH (OIDS=FALSE); And the trigger definition is here: CREATE TRIGGER aw_archival_is2 BEFORE UPDATE OR INSERT OR DELETE ON i_s FOR EACH ROW EXECUTE PROCEDURE aw_archive_test(); And the trigger function: CREATE OR REPLACE FUNCTION aw_archive_test() RETURNS "trigger" AS $BODY$ BEGIN RAISE WARNING 'Starting isa trigger for %', TG_OP; IF (TG_OP = 'UPDATE') THEN RAISE WARNING 'Calling insert_isa with update'; ELSIF (TG_OP = 'INSERT') THEN RAISE WARNING 'Calling insert_isa with insert'; ELSIF (TG_OP = 'DELETE') THEN RAISE WARNING 'Calling insert_isa with delete'; END IF;RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The trigger is enabled. Yes, this does intentionally nothing. The real code will obviously take care of data change in proper subtables. Well, the trigger should do nothing now . . . What I cannot get round to understanding is that an insert attempt will nicely give me two warnings, and will not insert, as expected with this code: WARNING: Starting isa trigger for INSERT WARNING: Calling insert_isa with insert Query returned successfully: 0 rows affected, 31 ms execution time. But an attempt to update actually magically goes to the proper subtable and performs the update: Query returned successfully: 1 rows affected, 16 ms execution time. Where did I deserve this?? ;-) In attempts to solve this I did mess around with trigger and trigger function definitions a bit. Could there be funny traces of this? What is the best way to analyse this behavior? I am testing from a pgAdmin 1.8 setup. The function you've shown won't do anything because BEFORE row triggers that return NULL don't do anything (for that row). If you want the operation to continue without any modification then just return NEW. Erik Jones ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
