Re: [GENERAL] Optimizing Queries Joining Several Views
Jason Long writes: > In order to do some complex calculations I have joined several views. > Each view could join quite a few tables. > The user is allowed to filter the results with several multi-select > input fields and this is used in the query as where a.id in > (:listOfIds). > This works fine if the user does not filter the results. These calcs > for every row in the entire can be calculated in 1-2 seconds. Certain > combinations of filters will make the query take up to 4 minutes and > will freeze the system until it has completed. Queries without these > calcs at all, but using the same filters work in a reasonable amount of > time. You're much more likely to get useful comments if you provide a concrete example and EXPLAIN ANALYZE results for the various cases. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to push predicate down
I wrote: > Hmm. The code explicitly won't push conditions down through an EXCEPT: > * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push > * quals into it, because that could change the results. > I remember coming to the conclusion that this is safe for > UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why > I thought that --- it seems like a qual that suppresses specific rows > should suppress all matching copies. I dug in the archives and found the discussion that led up to the current behavior; see thread starting here http://archives.postgresql.org/pgsql-hackers/2002-08/msg00041.php The issue is that rows that are "equal" according to the rules used by UNION/INTERSECT/EXCEPT may nonetheless be distinguishable to the expression in the upper WHERE clause, and if that's the case, pushing down the WHERE can lead to provably incorrect results. So the behavior is correct, or at least very difficult to improve on, as it stands. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preventing access temporarily.
On Thu, Jan 26, 2012 at 3:58 PM, Scott Marlowe wrote: > On Thu, Jan 26, 2012 at 3:55 PM, Gauthier, Dave > wrote: >> I found something else on the web. >> >> update pg_database set datallowconn = false where datname = 'foo'; >> update pg_database set datallowconn = true where datname = 'foo'; >> >> Seems to have worked OK. >> >> Thanks for the pg_hab.conf suggestion. I'll add that to my notes. > > Yeah either of those will work. pg_hba.conf is nice for more complex > setups and you can have several pg_hba.conf.whatever files laying > about, link the right one and reload. So it's pretty easy to script > and back out for complex stuff. Oh and also you can revoke connect by user which allows for finer grained control of connections as well. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multi master use case?
Hello, A client of ours has always had problems with slow internet connectivity - they are in a part of the country where that is a problem. There are a few hundred staff sharing a couple of asymmetric (ADSL) connections. One issue is with accessing their web-based Postgres app, which we host. Now they don't want to run it internally for a lot of the usual reasons, not least they have many distributed workers and trying to serve data from an already congested spot would be a non starter. Is this a case for multi master do you think? I.e. running one on the internet, one locally. Looking through the wiki http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling it seems there are a few solutions that have now gained maturity. Something like rubyrep sounds ideal. It would have to deal with a) a flaky local connection b) changing schemas (new tables, fields, views etc.) as well as data Create/update/delete frequencies are reasonably low, generally individuals updating single records so of the order of thousands per day max. Any experiences/thoughts? Oliver Kohll www.gtwm.co.uk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
On Thu, Jan 26, 2012 at 3:18 PM, Thomas Kellerer wrote: > Rodrigo E. De León Plicet wrote on 26.01.2012 22:52: > > Oracle claims it for releases going back to 7 >> > > Not true. > > Quote from the Oracle concepts manual: > > "Multiple-process Oracle (also called multiuser Oracle) uses several > processes to run different parts of the Oracle Database code and additional > processes for the users—either one process for each connected user or one > or more processes shared by multiple users. Most databases are multiuser > because a primary advantages of a database is managing data needed by > multiple users simultaneously." > Oracle offers intra-query parallelism. I am not entirely sure how they do it, but it is supported. I don't know if these subtasks are pthreads within the separate session process or if they are additional processes. Best Wishes, Chris Travers
Re: [GENERAL] How to push predicate down
I think in my case, It is safe to push the predicate down. Can someone please, examine the behavior of other databases.If it behaves like postgres, I will assume there are some cases where it can lead to wrong result set. I tried SQL server but my windows refuses it :-) Regards From: Tom Lane To: salah jubeh Cc: Volodymyr Kostyrko ; pgsql Sent: Thursday, January 26, 2012 5:47 PM Subject: Re: [GENERAL] How to push predicate down salah jubeh writes: > Sorry, The scenario, that I posted was not correct. I have traced it and the > union was not the problem, As I said the query excusion plan is over 5000 > line. I have created a scenario which similar to the scenario causes the > problem I have. > [ query uses EXCEPT not UNION ] Hmm. The code explicitly won't push conditions down through an EXCEPT: * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push * quals into it, because that could change the results. I remember coming to the conclusion that this is safe for UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why I thought that --- it seems like a qual that suppresses specific rows should suppress all matching copies. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
On Thu, Jan 26, 2012 at 3:02 PM, Merlin Moncure wrote: > On Thu, Jan 26, 2012 at 3:52 PM, Rodrigo E. De León Plicet > wrote: > > Quote: > > > > == > > > > This thread > > > > > http://postgresql.1045698.n5.nabble.com/Multithread-Query-Planner-td5143643.html > > > > was mentioned in a performance sub-group posting. Give it a read. > > > > Back? It means, so far as I can see, that PG is toast. It will fall > > down to being the cheap and dirty alternative to MySql, which even > > has, at least two, multi-threaded engines. DB2 switched it's *nix > > engine to threads from processes with release 9.5. Oracle claims it > > for releases going back to 7 (I haven't tried to determine which parts > > or applications; Larry has bought so many tchochtkes over the > > years...). SQL Server is threaded. > > > > Given that cpu's are breeding threads faster than cores, > > PG will fall into irrelevance. > > The author of that post apparently doesn't understand that even though > postgresql hasn't 'switched to threads', it can still do more than one > thing at once. Each process is itself an execution thread. A > multi-threaded query planner is perfectly possible in postgresql > architecture -- however each one must reside in it's own process and > you have to use shared memory instead instead of pthreads and locking. > Big whoop. The only thing at stake with a multi threaded planner is > optimizing single user tasks which is, while important, a niche > optimization. PostgreSQL is for more scalable than mysql for > multi-user loads and the gap is increasing. > > > There are cases where intraquery parallelism would be helpful. As far as I understand it, PostgreSQL is the only major, solid (i.e. excluding MySQL) RDBMS which does not offer some sort of intraquery parallelism, and when running queries across very large databases, it might be helpful to be able to, say, scan different partitions simultaneously using different threads. So I think it is wrong to simply dismiss the need out of hand. The thing though is that I am not sure that where this need really comes to the fore, it is typical of single-server instances, and so this brings me to the bigger question. The question in my mind though is a more basic one: How should intraquery parallelism be handled? Is it something PostgreSQL needs to do or is it something that should be the work of an external project like Postgres-XC? Down the road is there value in merging the codebases, perhaps making stand-alone/data/coordination node a compile time option? Obviously such is not a question that needs to be addressed now. We can wait until someone has something that is production-ready and relatively feature-complete before discussing merging projects. Best Wishes, Chris Travers
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
Rodrigo E. De León Plicet wrote on 26.01.2012 22:52: Oracle claims it for releases going back to 7 Not true. Quote from the Oracle concepts manual: "Multiple-process Oracle (also called multiuser Oracle) uses several processes to run different parts of the Oracle Database code and additional processes for the users—either one process for each connected user or one or more processes shared by multiple users. Most databases are multiuser because a primary advantages of a database is managing data needed by multiple users simultaneously." [...] "For each user connection, the application is run by a client process that is different from the dedicated server process that runs the database code. Each client process is associated with its own server process" Taken from: http://docs.oracle.com/cd/E11882_01/server.112/e25789/process.htm#i16977 So the Oracle architecture is very similar to the one that PostgreSQL uses - at least on Linux/Unix. On Windows this is done using threads (I think this is because Windows is not as efficient in running multiple processes as Linux/Unix). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
On Thu, Jan 26, 2012 at 3:52 PM, Rodrigo E. De León Plicet wrote: > Quote: > > == > > This thread > > http://postgresql.1045698.n5.nabble.com/Multithread-Query-Planner-td5143643.html > > was mentioned in a performance sub-group posting. Give it a read. > > Back? It means, so far as I can see, that PG is toast. It will fall > down to being the cheap and dirty alternative to MySql, which even > has, at least two, multi-threaded engines. DB2 switched it's *nix > engine to threads from processes with release 9.5. Oracle claims it > for releases going back to 7 (I haven't tried to determine which parts > or applications; Larry has bought so many tchochtkes over the > years...). SQL Server is threaded. > > Given that cpu's are breeding threads faster than cores, > PG will fall into irrelevance. The author of that post apparently doesn't understand that even though postgresql hasn't 'switched to threads', it can still do more than one thing at once. Each process is itself an execution thread. A multi-threaded query planner is perfectly possible in postgresql architecture -- however each one must reside in it's own process and you have to use shared memory instead instead of pthreads and locking. Big whoop. The only thing at stake with a multi threaded planner is optimizing single user tasks which is, while important, a niche optimization. PostgreSQL is for more scalable than mysql for multi-user loads and the gap is increasing. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preventing access temporarily.
On Thu, Jan 26, 2012 at 3:55 PM, Gauthier, Dave wrote: > I found something else on the web. > > update pg_database set datallowconn = false where datname = 'foo'; > update pg_database set datallowconn = true where datname = 'foo'; > > Seems to have worked OK. > > Thanks for the pg_hab.conf suggestion. I'll add that to my notes. Yeah either of those will work. pg_hba.conf is nice for more complex setups and you can have several pg_hba.conf.whatever files laying about, link the right one and reload. So it's pretty easy to script and back out for complex stuff. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preventing access temporarily.
I found something else on the web. update pg_database set datallowconn = false where datname = 'foo'; update pg_database set datallowconn = true where datname = 'foo'; Seems to have worked OK. Thanks for the pg_hab.conf suggestion. I'll add that to my notes. -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Thursday, January 26, 2012 5:39 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Preventing access temporarily. On Thu, Jan 26, 2012 at 3:05 PM, Gauthier, Dave wrote: > PG V9.0.1 on Linux > > > > I want to temporarily prevent users from connecting to a DB, let the > existing connections finish, , re-enable connections. > > What's the best way to do that? Edit pg_hba.conf to reject all connections and reload. current connections will stay connected, new ones will be refused. use pg_stat_activity to monitor connections til they're all gone / idle. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Preventing access temporarily.
On Thu, Jan 26, 2012 at 3:05 PM, Gauthier, Dave wrote: > PG V9.0.1 on Linux > > > > I want to temporarily prevent users from connecting to a DB, let the > existing connections finish, , re-enable connections. > > What's the best way to do that? Edit pg_hba.conf to reject all connections and reload. current connections will stay connected, new ones will be refused. use pg_stat_activity to monitor connections til they're all gone / idle. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Don't Thread On Me (PostgreSQL related)
Quote: == This thread http://postgresql.1045698.n5.nabble.com/Multithread-Query-Planner-td5143643.html was mentioned in a performance sub-group posting. Give it a read. Back? It means, so far as I can see, that PG is toast. It will fall down to being the cheap and dirty alternative to MySql, which even has, at least two, multi-threaded engines. DB2 switched it's *nix engine to threads from processes with release 9.5. Oracle claims it for releases going back to 7 (I haven't tried to determine which parts or applications; Larry has bought so many tchochtkes over the years...). SQL Server is threaded. Given that cpu's are breeding threads faster than cores, PG will fall into irrelevance. == Source: http://drcoddwasright.blogspot.com/2012/01/dont-thread-on-me.html Comments? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Preventing access temporarily.
PG V9.0.1 on Linux I want to temporarily prevent users from connecting to a DB, let the existing connections finish, , re-enable connections. What's the best way to do that? Thanks in Advance
Re: [GENERAL] Best way to create unique primary keys across schemas?
On Fri, Jan 27, 2012 at 4:56 AM, panam wrote: > Thanks, yeah, but the dummy tables are needed anyway in my case for those > entities that are shared among the tenants :) Ah! Then that's easy :) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help needed creating a view
Hello ,
if you need to construct view with the columns math, physics , I think what
you need is crosstab function
Regards
From: David Johnston
To: 'Sebastian Tennant' ; pgsql-general@postgresql.org
Sent: Thursday, January 26, 2012 8:50 PM
Subject: Re: [GENERAL] Help needed creating a view
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sebastian Tennant
Sent: Thursday, January 26, 2012 6:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Help needed creating a view
Hi list,
Given an 'applications' table for a static set of courses::
user_id (integer)
course_name (text)
completed (boolean)
how best should I go about creating an 'alumni' view with columns:
user_id (integer)
maths (boolean)
english (boolean)
. .
. .
. .
where each of the columns (apart from user_id) is a boolean value
representing whether or not user_id completed each course?
Sebastian
-
A) SELECT user_id, CASE WHEN course_name = 'Maths' THEN completed ELSE false
END math_cmp, CASE WHEN course_name = 'English' THEN completed ELSE false
END AS english_cmp FROM applications
a) Expand to multiple columns and store either the default "false" or the
value of "completed" into the value for the corresponding column
B) SELECT user_id, CASE WHEN bool_or(math_cmp) THEN true ELSE false END AS
did_math, CASE WHEN bool_or(english_cmp) THEN true ELSE false END AS
did_english FROM "A" GROUP BY user_id
b) Then determine whether the user_id has at least one "true" in the given
column by using the "bool_or" function
Dynamic columns are difficult to code in SQL. You should probably also
include some kind of "OTHER COMPLETED DISCIPLINES" column to catch when you
add an previously unidentified course - "course_name NOT IN
('Maths','English','...')"
Also concerned with the fact that, as coded, a single complete course
triggers the given flag. What happens when you want to specify that they
have only completed 3 of 4 courses? Also, instead of hard-coding the
"course_name" targets you may want to do something like "CASE WHEN
course_name IN (SELECT course_name FROM courses WHERE course_type =
'Maths')".
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger
Hello. PostgreSQL 8.4. I am trying to create a trigger which removes "old" rows from the table just before inserting a new one. The table has an UNIQUE INDEX on a column, that's why I need to remove an old item with the same value of the column before inserting a new one. If I work without transactions (in auto-commit mode), all seems to be fine. But something strange is happened when I use transactions. The following SQL represents the problem. How to avoid strange "duplicate key value violates unique constraint" error (with minimum locking level)?.. And why this error happens at all? -- Prepare the fixture. create table a(i integer); CREATE UNIQUE INDEX a_idx ON a USING btree (i); CREATE FUNCTION a_tr() RETURNS trigger AS $body$ BEGIN DELETE FROM a WHERE i = NEW.i; RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; CREATE TRIGGER a_tr BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE a_tr(); -- Check if the trigger really works. No unique constraint errors are thrown. insert into a values(1); insert into a values(1); --> ok -- NOW IN CONNECTION (A): begin; insert into a values(1); --> do not commit! -- THEN IN CONNECTION (B): insert into a values(1); --> it hangs, because the connection (A) is not committed - ok -- NOW IN CONNECTION (A) AGAIN: commit; --> ok -- WE SEE AT CONNECTION (B) THE THE FOLLOWING IMMEDIATELY: ERROR: duplicate key value violates unique constraint "a_idx"
Re: [GENERAL] Help needed creating a view
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sebastian Tennant
Sent: Thursday, January 26, 2012 6:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Help needed creating a view
Hi list,
Given an 'applications' table for a static set of courses::
user_id (integer)
course_name (text)
completed (boolean)
how best should I go about creating an 'alumni' view with columns:
user_id (integer)
maths (boolean)
english (boolean)
. .
. .
. .
where each of the columns (apart from user_id) is a boolean value
representing whether or not user_id completed each course?
Sebastian
-
A) SELECT user_id, CASE WHEN course_name = 'Maths' THEN completed ELSE false
END math_cmp, CASE WHEN course_name = 'English' THEN completed ELSE false
END AS english_cmp FROM applications
a) Expand to multiple columns and store either the default "false" or the
value of "completed" into the value for the corresponding column
B) SELECT user_id, CASE WHEN bool_or(math_cmp) THEN true ELSE false END AS
did_math, CASE WHEN bool_or(english_cmp) THEN true ELSE false END AS
did_english FROM "A" GROUP BY user_id
b) Then determine whether the user_id has at least one "true" in the given
column by using the "bool_or" function
Dynamic columns are difficult to code in SQL. You should probably also
include some kind of "OTHER COMPLETED DISCIPLINES" column to catch when you
add an previously unidentified course - "course_name NOT IN
('Maths','English','...')"
Also concerned with the fact that, as coded, a single complete course
triggers the given flag. What happens when you want to specify that they
have only completed 3 of 4 courses? Also, instead of hard-coding the
"course_name" targets you may want to do something like "CASE WHEN
course_name IN (SELECT course_name FROM courses WHERE course_type =
'Maths')".
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help needed creating a view
Hi list, Given an 'applications' table for a static set of courses:: user_id (integer) course_name (text) completed (boolean) how best should I go about creating an 'alumni' view with columns: user_id (integer) maths (boolean) english (boolean) . . . . . . where each of the columns (apart from user_id) is a boolean value representing whether or not user_id completed each course? Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimizing Queries Joining Several Views
In order to do some complex calculations I have joined several views. Each view could join quite a few tables. The user is allowed to filter the results with several multi-select input fields and this is used in the query as where a.id in (:listOfIds). This works fine if the user does not filter the results. These calcs for every row in the entire can be calculated in 1-2 seconds. Certain combinations of filters will make the query take up to 4 minutes and will freeze the system until it has completed. Queries without these calcs at all, but using the same filters work in a reasonable amount of time. I have considered the following ways to make this faster. 1. increase geqo_threshold, from_collapse_limit, join_collapse_limit While this does improve the performance on some of the more complex queries, generally others suffer. 2. Filter the results first and then join the complex calcs. The database is small. About 1 GB on disk and the vast majority of that is taken by bytea documents that are never accessed. From what I can tell all data is in shared buffers. Any advice would be greatly appreciated. Here are the settings I have changed in postgresql.conf statement_timeout = 60 # in milliseconds, 0 is disabled geqo_effort = 10# range 1-10 default_statistics_target = 1 geqo_threshold = 13 from_collapse_limit = 9 join_collapse_limit = 9# 1 disables collapsing of explicit JOIN clauses work_mem = 48MB # pgtune wizard 2011-12-12 maintenance_work_mem = 480MB # pgtune wizard 2011-12-12 shared_buffers = 1920MB # pgtune wizard 2011-12-12 effective_cache_size = 5632MB # pgtune wizard 2011-12-12 seq_page_cost = 0.005# measured on an arbitrary scale random_page_cost = 0.005 # same scale as above -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Let-bindings in SQL statements
On Thu, Jan 26, 2012 at 3:37 PM, Jon Smark wrote: > Hi, > > Is it possible to do the equivalent of let-bindings in a pure SQL function? > I have a SELECT that invokes "now" multiple times. It would be nicer > to do it only once and reuse the value. Something like this: > > LET right_now = SELECT now () IN > SELECT * FROM my_table WHERE right_now >= start AND ... > > In PL/pgSQL this is easy, but I wonder about SQL... > > WITH param AS ( select now() as p_start, somefunc() as p_something ) SELECT * FROM param,my_table WHERE right_now >= param.p_start AND ...
Re: [GENERAL] Best way to create unique primary keys across schemas?
Thanks, yeah, but the dummy tables are needed anyway in my case for those entities that are shared among the tenants :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Best-way-to-create-unique-primary-keys-across-schemas-tp5165043p5433562.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to push predicate down
salah jubeh writes: > Sorry, The scenario, that I posted was not correct. I have traced it and the > union was not the problem, As I said the query excusion plan is over 5000 > line. I have created a scenario which similar to the scenario causes the > problem I have. > [ query uses EXCEPT not UNION ] Hmm. The code explicitly won't push conditions down through an EXCEPT: * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push * quals into it, because that could change the results. I remember coming to the conclusion that this is safe for UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why I thought that --- it seems like a qual that suppresses specific rows should suppress all matching copies. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Let-bindings in SQL statements
On Thu, 26 Jan 2012 06:37:49 -0800 (PST), Jon Smark wrote about [GENERAL] Let-bindings in SQL statements: >Is it possible to do the equivalent of let-bindings in a pure SQL >function? I have a SELECT that invokes "now" multiple times. It would >be nicer to do it only once and reuse the value. Something like this: > >LET right_now = SELECT now () IN >SELECT * FROM my_table WHERE right_now >= start AND ... > >In PL/pgSQL this is easy, but I wonder about SQL... Try using CURRENT_TIMESTAMP instead. In fact, CURRENT_TIMESTAMP is more traditional SQL than now(). I don't have an ANSI standard handy, so I cannot be certain when now() was added, if ever; but I have been using CURRENT TIMESTAMP (space instead of vinculum) under DB2 for 20 years or more. -- Regards, Dave [RLU #314465] *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* dwn...@ntlworld.com (David W Noon) *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* signature.asc Description: PGP signature
Re: [GENERAL] Composite Type : pros and cons
On Thu, Jan 26, 2012 at 3:22 AM, Leguevaques Alex wrote: > > Hello, > I'm new to Pg and exploring its advanced functionalities for a project. > I find composite type very interesting, but what are problems/limitations ? > I'd want to create this structure for example: Composite types add a little value in that you can apply frequently grouped together fields in a table. This can save a little typing. The downside is you are diverging from classic sql mechanics a little bit which can make some things awkward. Where they really shine though is as variables if you are doing a lot of backend programming with functions. You can pass them too and from functions and make arrays out of them...this is very powerful once you get the hang of it. If you are using a client stack that understands postgres composite types, you can (ab)use this to send complicated datasets to and from the database. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to push predicate down
Sorry, The scenario, that I posted was not correct. I have traced it and the
union was not the problem, As I said the query excusion plan is over 5000 line.
I have created a scenario which similar to the scenario causes the problem I
have.
CREATE TABLE TEST (
ID SERIAL PRIMARY KEY,
COL1 TEXT,
COL2 INT,
COL3 TEXT
);
CREATE TABLE TEST_REMOVE (
COL1 TEXT,
COL3 TEXT
);
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('foo', 7 , 'Bar');
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('foo', 8 , 'Bar');
INSERT INTO TEST (COL1, COL2, COL3) VALUES ('Boo', 8 , 'Far');
INSERT INTO TEST_REMOVE (COL1, COL3) VALUES ('foo', 'Bar');
CREATE OR REPLACE VIEW REMOVED_TEST_ENTRIES AS
SELECT T.*
FROM TEST T JOIN TEST_REMOVE TR ON(T.COL1 = TR.COL1 AND T.COL3 = TR.COL3)
CREATE OR REPLACE VIEW TEST_ENTRIES AS
SELECT * FROM TEST
EXCEPT
SELECT * FROM REMOVED_TEST_ENTRIES
;
EXPLAIN ANALYZE SELECT * FROM TEST_ENTRIES WHERE COL3 = 'BAR';
"Subquery Scan test_entries (cost=195.40..206.64 rows=1 width=72) (actual
time=0.140..0.140 rows=0 loops=1)"
" Filter: (test_entries.col3 = 'BAR'::text)"
" -> SetOp Except (cost=195.40..205.61 rows=82 width=72) (actual
time=0.134..0.135 rows=1 loops=1)"
" -> Sort (cost=195.40..197.44 rows=817 width=72) (actual
time=0.119..0.124 rows=5 loops=1)"
" Sort Key: "*SELECT* 1".id, "*SELECT* 1".col1, "*SELECT* 1".col2,
"*SELECT* 1".col3"
" Sort Method: quicksort Memory: 25kB"
" -> Append (cost=0.00..155.88 rows=817 width=72) (actual
time=0.016..0.098 rows=5 loops=1)"
" -> Subquery Scan "*SELECT* 1" (cost=0.00..26.00 rows=800
width=72) (actual time=0.014..0.024 rows=3 loops=1)"
" -> Seq Scan on test (cost=0.00..18.00 rows=800
width=72) (actual time=0.009..0.013 rows=3 loops=1)"
" -> Subquery Scan "*SELECT* 2" (cost=117.09..129.88
rows=17 width=72) (actual time=0.045..0.061 rows=2 loops=1)"
" -> Merge Join (cost=117.09..129.71 rows=17
width=72) (actual time=0.043..0.054 rows=2 loops=1)"
" Merge Cond: ((t.col1 = tr.col1) AND (t.col3 =
tr.col3))"
" -> Sort (cost=56.58..58.58 rows=800
width=72) (actual time=0.022..0.025 rows=3 loops=1)"
" Sort Key: t.col1, t.col3"
" Sort Method: quicksort Memory: 25kB"
" -> Seq Scan on test t
(cost=0.00..18.00 rows=800 width=72) (actual time=0.002..0.005 rows=3 loops=1)"
" -> Sort (cost=60.52..62.67 rows=860
width=64) (actual time=0.010..0.012 rows=1 loops=1)"
" Sort Key: tr.col1, tr.col3"
" Sort Method: quicksort Memory: 25kB"
" -> Seq Scan on test_remove tr
(cost=0.00..18.60 rows=860 width=64) (actual time=0.003..0.004 rows=1 loops=1)"
"Total runtime: 0.213 ms"
From: Volodymyr Kostyrko
To: salah jubeh
Cc: pgsql
Sent: Thursday, January 26, 2012 3:49 PM
Subject: Re: [GENERAL] How to push predicate down
salah jubeh wrote:
>
> Hello Guys,
>
> In the past I had a view defined as follows
>
> CREATE view abcd as
> SELECT whatever .. --- query1
>
> Some business requierments came up and I had to change it like this
>
> CREATE view abcd as
> SELECT whatever .. --- query1
> UNION
> SELECT whatever .. query2
1. You sure you need UNION and not UNION ALL?
2. Can you post more detail example?
For example:
select anything from first_table
union
select anything from second_table
where anything == something;
This way WHERE clause is a part of second subselect and will not be
propagated to the first one.
--
Sphinx of black quartz judge my vow.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Let-bindings in SQL statements
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jon Smark Sent: Thursday, January 26, 2012 9:38 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Let-bindings in SQL statements Hi, Is it possible to do the equivalent of let-bindings in a pure SQL function? I have a SELECT that invokes "now" multiple times. It would be nicer to do it only once and reuse the value. Something like this: LET right_now = SELECT now () IN SELECT * FROM my_table WHERE right_now >= start AND ... In PL/pgSQL this is easy, but I wonder about SQL... Thanks in advance! Jon --- No, not really. In some cases you can use a CTE (WITH) clause to create a single row with whatever names and values you need and then, using Sub-Selects or CROSS JOIN, introduce that row into the appropriate parts of the query. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to push predicate down
salah jubeh wrote: Hello Guys, In the past I had a view defined as follows CREATE view abcd as SELECT whatever .. --- query1 Some business requierments came up and I had to change it like this CREATE view abcd as SELECT whatever .. --- query1 UNION SELECT whatever .. query2 1. You sure you need UNION and not UNION ALL? 2. Can you post more detail example? For example: select anything from first_table union select anything from second_table where anything == something; This way WHERE clause is a part of second subselect and will not be propagated to the first one. -- Sphinx of black quartz judge my vow. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Let-bindings in SQL statements
On 26 January 2012 15:37, Jon Smark wrote: > Hi, > > Is it possible to do the equivalent of let-bindings in a pure SQL function? > I have a SELECT that invokes "now" multiple times. It would be nicer > to do it only once and reuse the value. Something like this: > > LET right_now = SELECT now () IN > SELECT * FROM my_table WHERE right_now >= start AND ... > > In PL/pgSQL this is easy, but I wonder about SQL... > > Thanks in advance! > Jon > > In fact now() is a little bit tricky here. now() returns the time when the transaction started, so if you run `begin;` and call now() multiple times (even in different queries, but within the same transaction), the function will return the same value. regards Szymon
[GENERAL] Let-bindings in SQL statements
Hi, Is it possible to do the equivalent of let-bindings in a pure SQL function? I have a SELECT that invokes "now" multiple times. It would be nicer to do it only once and reuse the value. Something like this: LET right_now = SELECT now () IN SELECT * FROM my_table WHERE right_now >= start AND ... In PL/pgSQL this is easy, but I wonder about SQL... Thanks in advance! Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to push predicate down
Hello Guys, In the past I had a view defined as follows CREATE view abcd as SELECT whatever .. --- query1 Some business requierments came up and I had to change it like this CREATE view abcd as SELECT whatever .. --- query1 UNION SELECT whatever .. query2 Now I have a problem in the time for calculating the query when using a predicate -- this time makes sense SELECT * FROM abcd Query time ( Past) = X Query time (current) = X +Y -- (Y is the time which introduced by query2) But If I run the query -- This does not make sense SELECT * FROM abcd where predicate = 'predicate_a' Query time ( Past) = 1 /10 * X Query time (current) = X + Y -- I assume the time should be 1/10*X + Y --Note, Y is much smaller than X so I do not care too much about it, so X is the dominant factor I had a look on the execution plane and the predicate 'predicate_a' was pushed up on the top in the current situation i.e. In the past the excution plane was like this Filter using the predicate 'predicate_a' and then do the calculation of the rest of query1, this is why the time is reduced to 1/10 * X Now the execution plan is like this Calculate query1 and then calculate query2 and then Union the result and finally filter using predicate 'predicate_a', Why it is not like this Filter first using the predicate 'predicate_a' when calculating query1 Filter first using the predicate 'predicate_a' when calculating query2 Then do the union Sorry I did not post the execution plan but it is more than 5000 line Regards
Re: [GENERAL] Best way to create unique primary keys across schemas?
On Thu, Jan 26, 2012 at 2:12 AM, panam wrote:
> CREATE TABLE tbl (ID bigint default nextval('global_seq') primary key,foo
> varchar,bar varchar); --in public schema
> CREATE TABLE schema1.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
> sequence in public schema
> CREATE TABLE schema2.tbl (LIKE public.tbl INCLUDING ALL); --draws ids from
> sequence in public schema
Yep, but you can do this more simply:
CREATE TABLE schema1.tbl (ID bigint default nextval('global_seq')
primary key,foo varchar,bar varchar)
CREATE TABLE schema2.tbl (ID bigint default nextval('global_seq')
primary key,foo varchar,bar varchar)
No need for the dummy table.
Obviously you would want better names than these ("global_seq" is a
really dumb name for a production environment!), but you knew that
already :)
ChrisA
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Composite Type : pros and cons
Hello, I'm new to Pg and exploring its advanced functionalities for a project. I find composite type very interesting, but what are problems/limitations ? I'd want to create this structure for example: Phone Nom du champ Type Accès Clef Commentaire typ_tel integer type téléphone (liste 25) tel_pre varchar(5) Préfixe international téléphone tel_num varchar(14) tel_ext varchar(5) Numéro de poste and other structures for address and timestamp fields (creation and update timestamps. Is it a good idea ? Are index, constraints possible/easy on subfields ? Thank you
