Re: [SQL] Performance of NOT IN and <> with PG 9.0.4
On Tue, May 24, 2011 at 5:10 PM, Jasmin Dizdarevic wrote: > That's strange... > If I comment out these rows > --sum(coalesce(e.num_wert,0)), > --sum(coalesce(d.num_wert,0)) > in the given statement, it works fine with enable_material = 'on'. > I didn't change any join. That's not that strange. The planner thinks that the cost of the plan you're getting with enable_material=on is 729721.34, and the cost of the plan you're getting with enable_material=off is 727904.40, or approximately an 0.2% difference. Any little change you make to anything in the system, or just random changes in your statistics, could cause the plans to bounce back and forth between those two. The real question is why the planner thinks those two cost about the same, when in reality one of them is way faster than the other. You might want to look through the EXPLAIN ANALYZE output and try to figure out which part of the plan is being mis-estimated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?
Hello, I'd like to have more comments about the following case: . 500KB per PDF file; 30 files per year . PSQL8.3 . struts2.2.3 + mybatis for sql operation . tomcat6 Added more info Solution: (1) Save pdfs to file system, only point file name in psql8.3 (2) Save oids of pdfs into table (3) Save pdf files as bytea column in psql8.3 Pros and cons for (1), (2), (3), which is the most efficient way? Thanks a lot! Emi -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 500KB PDF files for postgresql8.3, which is the most efficient way?
Hello, I'd like to have more comments about the following case: . 500KB per PDF file; 30 files per year . PSQL8.3 Solution: (1) Save pdfs to file system, only point file name in psql8.3 (2) Save oids of pdfs into table (3) Save pdf files as bytea column in psql8.3 Pros and cons for (1), (2), (3), which is the most efficient way? Thanks a lot! Emi On 05/27/2011 12:45 AM, Jasen Betts wrote: On 2011-05-26, Bosco Rama wrote: select * into temp table foo from maintable where primcol=123; update foo set primcol = 456; insert into maintable select * from foo; You also may need this is if you intend to use the same sequence of calls on within the same session: drop table foo; Yet another way to do the same thing: begin; create temportary table foo on commit drop as select * from maintable where primcol=123; update foo, set primcol=456; insert into maintable select * from foo; commit; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?
On Fri, May 27, 2011 at 9:15 AM, Emi Lu wrote: > > Solution: > > (1) Save pdfs to file system, only point file name in psql8.3 > > (2) Save oids of pdfs into table > > (3) Save pdf files as bytea column in psql8.3 > > > Pros and cons for (1), (2), (3), which is the most efficient way? > You asked about the "most efficient" but you didn't tell us what you are doing with the pdf files. It really doesn't matter how you store it if you are simply inserting a 500KB object 30 times per year - then it becomes all about convenience. I'd probably rule out solution #1 in that case purely from a maintenance perspective. Storing the PDFs in the db eliminates the maintenance work of ensuring that the files are archived along with the db backups, that file paths stay correct whenever a new db host is provisioned, and storing the files in the db provides transaction-safe file storage. If, on the other hand, you are selecting/updating the rows in question several hundred times per second or more, then you may well want to put some thought into efficiency and, assuming some kind of web access, providing your webserver/caching layer with direct access to the files on the filesystem for service efficiency. But if you are only occasionally accessing the files in question, there's little reason not to put them in the db. If mybatis can't load the object that references the file without also loading the entire file from the db - and you are using that object for other things that will require frequent loading/storing - then you probably don't want the files in the db. If it can load the object without loading the binary data, by lazily loading that column only when requested, then it doesn't matter. Using BLOBs guarantees that you can access the entire row without loading the binary data if mybatis exposes the blob separately from the rest of the object, but mybatis may be capable of issuing a select without that column and then grabbing that column as needed in order to simulate that in the case of a bytea column, anyway. Internally, a large bytea column is treated similarly to a blob, with any data over a certain size not stored in-line with the rest of the row for efficiency reasons.
[SQL] Order of evaluation in triggers for checks on inherited table partitions
I am trying to create a trigger on updates to a table that is
partitioned. The child tables are partitioned by month and include
checks on a timestamp field. I want the trigger on the updates to
call a function that replaces the update entirely. In order to do
this my trigger deletes the record from the parent table (which
deletes it from the appropriate child table) and then inserts into the
appropriate child table and returns NULL (thus skipping the actual
update). However when I try to update an existing record with a
timestamp that would place it in a child table different from the
child table it is in I get an error due to the check on the child
table it is currently in. My best guess as to what is happening is
that the trigger is evaluating the check before it evaluates the
trigger function and thus cannot tell that the update to the original
table should never take place. I have included an example below. The
error that results is "new row for relation "t_foo_2011_6" violates
check constraint "t_foo_2011_6_f_timestamp_check""
My questions:
Is the order of evaluation for the trigger causing this error?
If not what is?
Is there another way to update a record in a child table that would
move it to another child table before the update and skip the
evaluation of the check constraints on the current table?
Example code follows:
CREATE SCHEMA some_schema;
CREATE SCHEMA some_schema_children;
--master table
CREATE TABLE some_schema.t_foo (
f_id_foo serial,
f_timestamp timestamp,
f_text varchar(30)
);
CREATE OR REPLACE FUNCTION some_schema.foo_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_tablename varchar(13);
v_month integer;
v_year integer;
BEGIN
v_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));
v_year:=(SELECT date_part from date_part('year', NEW.f_timestamp));
v_tablename:='t_foo_'||v_year||'_'||v_month;
IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = v_tablename AND
schemaname='some_schema_children')=0)
THEN
IF (v_month=12)
THEN
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
ELSE
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
END IF;
END IF;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER some_schema_insert_foo_trigger
BEFORE INSERT ON some_schema.t_foo
FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_insert_trigger();
CREATE OR REPLACE FUNCTION some_schema.foo_update_trigger()
RETURNS TRIGGER AS $$
DECLARE
v_tablename varchar(13);
v_month integer;
v_year integer;
BEGIN
v_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));
v_year:=(SELECT date_part from date_part('year', NEW.f_timestamp));
v_tablename:='t_foo_'||v_year||'_'||v_month;
IF ((SELECT COUNT(*) FROM pg_tables WHERE tablename = v_tablename AND
schemaname='some_schema_children')=0)
THEN
IF (v_month=12)
THEN
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
ELSE
EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';
END IF;
END IF;
EXECUTE 'DELETE FROM some_schema.t_foo WHERE f_id_foo='||NEW.f_id_foo;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER some_schema_update_foo_trigger
BEFORE UPDATE ON some_schema.t_foo
FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_update_trigger();
INSERT INTO some
Re: [SQL] Order of evaluation in triggers for checks on inherited table partitions
On Fri, 27 May 2011 12:28:51 -0700 Kevin Crain wrote: >Is the order of evaluation for the trigger causing this error? Are you aware that triggers are executed in alphabetical order? I simply used RAISE to check the order of execution of my triggers: RAISE NOTICE '% % % %: received %', TG_TABLE_NAME, TG_NAME, TG_WHEN, TG_OP, NEW; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
