Re: [SQL] MOVE in SQL vs PLPGSQL
[EMAIL PROTECTED] wrote: I can't find a good way to skip over a large number of records in PLPGSQL (I want to fast-forward and I don't need the I/O of reading and throwing away hundreds of records.) In SQL, I could just use MOVE. That doesn't appear to be supported in PLPGSQL?! Help? Andrew - your question doesn't seem to make sense - can you give some more details? Are you having some problem with cursors in plpgsql? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] creating postgres tables by passing a string to function
Scott cox wrote: I am storing data separated by month. How do I perform Inserts and Selects based on something like this. select * from (select 'shipped' || '0509') AS aShippingTable The table 'shipped' || '0509' exists. I am scanning barcode labels which have the '0509' on them and I want to move the data to appropiate monthly tables. '0510' would represent shipped on 2005 october. Eventually I want to create, insert, select all based on passing a string such as '0509' to a function. Well, it sounds like you want to look at the EXECUTE command if you're thinking of writing functions in plpgsql. However, you don't say how you came to the conclusion that separating your shipping-table into hundreds of smaller tables was the best design. Can I ask what testing you did and what the results were? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] BirthDay SQL Issue
brett wrote:
I need to be able to select two dates on my webby so I have a range of
dates
Then I want to be able to get, using some nifty query, everyone's
birthday that falls between those two dates. So if someone's birthday
was 04/09/79 it would return that tuple
I have been stuck on this for ages. Any my search for help has brought
me here.
You need to look at it from a different angle - don't try comparing
dates, compare text instead.
=> SELECT to_char('2005-03-04'::date, 'MMDD');
to_char
-
0304
(1 row)
That should do it.
Oh - you might want to read up on functional indexes in the manual too,
if this is a query you'll be running regularly.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Getting user created tables from SQL
Cenk KIZILDAG wrote:
TableQuery->SQL->Add ("SELECT TABLE_NAME AS TNAME FROM
INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')");
CTableArr[f] = TableQuery->FieldByName ("TNAME")->AsString;
the first Select statement work correct, counttables variable gets
the right value but unfortunately, after the second select statement,
in C++ Builders SQL Explorer window, it brings the correct number of
rows but without datas in them!!! In Enterprise Manager the second
select statement also works correct but how come it aint work in C++
builder?
I don't suppose this could be an identifer case issue? In the first
query TNAME will be case-folded to 'tname' (because that's how PG does
case-insensitive identifiers).
Try looking up FieldByName("tname") and see if that comes up with anything.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Help with simple SQL query?
Hi, I have 2 tables with the following columns: order: order_id, order_price orderline: parent_order_id, orderline_price I want all orders order where _price <> sum(orderline_price). What is wrong with the following query: select order_id from order, orderline where order_id = parent_order_id and order_price <> (select sum(orderline_price) from orderline group by parent_order_id) It reports "ERROR: more than one row returned by a subquery used as an expression" which seems right (the select sum() returns multiple rows?), but I cannot get query right. Can someone help? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help with simple SQL query?
Joost -- You are correct in stating that the problem is that the subquery returns more than 1 row -- try using the NOT IN syntax ... it is not likely to be very efficient but at least avoids the syntax error: select order_id from order, orderline where order_id = parent_order_id and order_price NOT IN (select sum(orderline_price) from orderline group by parent_order_id) HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Joost Kraaijeveld Sent: Wed 10/5/2005 4:35 AM To: Pgsql-sql@postgresql.org Cc: Subject:[SQL] Help with simple SQL query? Hi, I have 2 tables with the following columns: order: order_id, order_price orderline: parent_order_id, orderline_price I want all orders order where _price <> sum(orderline_price). What is wrong with the following query: select order_id from order, orderline where order_id = parent_order_id and order_price <> (select sum(orderline_price) from orderline group by parent_order_id) It reports "ERROR: more than one row returned by a subquery used as an expression" which seems right (the select sum() returns multiple rows?), but I cannot get query right. Can someone help? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster !DSPAM:4343bb5c106941059188129! ---(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] Help with simple SQL query?
Hi Joost, Try joining and comparing the order table with/to an aggregated orderline table, something like this: select order_id, order_price, sum_price, order_price - sum_price as diff from order, (select parent_order_id, sum(orderline_price) as sum_price from orderline group by parent_order_id) as foo where order_id = parent_order_id and order_price != sum_price This should prove to be as efficient as it gets ;) -- Best, Frank. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help with simple SQL query?
On Wed, 2005-10-05 at 14:21 +0200, Frank van Vugt wrote: > Hi Joost, > > Try joining and comparing the order table with/to an aggregated orderline > table, something like this: > > select order_id, order_price, sum_price, order_price - sum_price as diff > from order, (select parent_order_id, sum(orderline_price) as sum_price > from orderline group by parent_order_id) as foo > where order_id = parent_order_id > and order_price != sum_price > > > This should prove to be as efficient as it gets ;) How nice. An answer between the questions. And a working anser!. Which manual did I miss ;-) Thanks. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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] BirthDay SQL Issue
brett <[EMAIL PROTECTED]> schrieb:
> Hi there
>
> This is my first posting here, please forgive me if I make any
> mistakes here.
>
> Ok
>
> I have the structure
>
> {CLIENTS} = Client ID, First Name, Surname, DOB, Address, Home Phone
> No, Mobile Phone No
>
> As one relation / table (There are several others in this db that are
> not related to this issue)
>
> My problem is this
>
> I need to be able to select two dates on my webby so I have a range of
> dates
I wrote a function for me similar to your problem. You can visit it:
http://a-kretschmer.de/tools/birthday.sql
Maybe it is useful for you. It need one argument: a integer, and returns
a list of people who has birthday between CURRENT_DATE and this+N days.
Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] MOVE in SQL vs PLPGSQL
[EMAIL PROTECTED] wrote: Andrew - your question doesn't seem to make sense - can you give some more details? Are you having some problem with cursors in plpgsql? Yes. I'd like the effect of the plain SQL MOVE with a PLPGSQL cursor. I'm writing a procedure for which SQL is inadequate (I need condiitonal branches and looping). However, in PLPGSQL, it appears the only way to fast forward on a cursor is a repeated FETCH, which is wasteful. PLPGSQL doesn't even allow me to set up a SQL-type cursor using EXECUTE--I have to use the refcursor datatype and it doesn't appear to support MOVE. I could write a procedure client-side in (say) Java without any problem using absolute or relative move, but I'd like the procedure to run on the server. Ah, now I see what you're saying. You're quite right in your suspicions, "MOVE..." isn't supported for plpgsql cursors. You could probably do something with EXECUTE and returning a refcursor from a previous function, but that sounds fiddly. I must admit, on the odd occasion I want to skip a row, I just FETCH it and move on. Anyone else? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] using pg_tables and tablename in queries
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > solarsail <[EMAIL PROTECTED]> writes: >> I have a large number of tables with a common naming convention > >> mytable001, mytable002, mytable003 ... mytable00n > >> I would like to do a query across all of the tables, however I do not >> know >> all of the tables before hand, and I do not want to ( cant ) manually >> generate a query like > >> select * from mytable001, mytable002, mytable003 > >> I have a query that returns the names of the tables I want to query: > >> select tablename from pg_tables where tablename like 'mytable%' > > This looks to me like a situation in which you should rethink your > data design. Those tables should all get merged into one big table, > adding one extra column that reflects what you had been using to > segregate the data into different tables. > > regards, tom lane Sounds like a classic opportunity to use the postgreSQL inheritance model to me > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] MOVE in SQL vs PLPGSQL
Richard Huxton writes: > Ah, now I see what you're saying. You're quite right in your suspicions, > "MOVE..." isn't supported for plpgsql cursors. You could probably do > something with EXECUTE and returning a refcursor from a previous > function, but that sounds fiddly. > I must admit, on the odd occasion I want to skip a row, I just FETCH it > and move on. Anyone else? There is something on the TODO list about improving plpgsql's cursor functionality --- there's no reason it shouldn't have MOVE, except that no one got around to it yet. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] BirthDay SQL Issue
On Sunday 02 Oct 2005 4:02 am, brett wrote: > This is my first posting here, please forgive me if I make any > mistakes here. thought your question was dead simple. worked on the answer for hours at end. learnt a lot of sql, but couldnt solve it. Looks like a solution is out. All i can say is - we need more newbies like you! -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Use of partial index
I'm a little confused about partial indexes. I have a couple of tables, like this: CREATE TABLE events ( event_idINTEGER PRIMARY KEY, tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), place_fkINTEGER REFERENCES places (place_id), event_date CHAR(18) NOT NULL DEFAULT '31', sort_date DATE NOT NULL DEFAULT '40041024BC', event_text TEXT NOT NULL DEFAULT '', sentenceTEXT NOT NULL DEFAULT '' ); To this table I have created a partial index: CREATE INDEX events_born ON events (tag_type_fk) WHERE tag_type_fk = 2; Another table: CREATE TABLE participants ( -- the TMG 'E' file participant_id INTEGER PRIMARY KEY, person_fk INTEGER REFERENCES persons (person_id), event_fkINTEGER REFERENCES events (event_id), role_type_fkINTEGER REFERENCES role_types (role_type_id), is_principalBOOLEAN NOT NULL DEFAULT 'f', is_primary_eventBOOLEAN NOT NULL DEFAULT 'f', participant_noteTEXT NOT NULL DEFAULT '', participant_nameTEXT NOT NULL DEFAULT '', age_meanINTEGER NOT NULL DEFAULT 0, age_deviINTEGER NOT NULL DEFAULT 0, CONSTRAINT person_event UNIQUE (person_id, event_id) ); And a view: CREATE OR REPLACE VIEW principals AS SELECT participants.person_fk AS person, events.event_id AS event, events.place_fk AS place, events.event_date AS event_date, events.sort_date AS sort_date, events.tag_type_fk AS tag_type FROM events, participants WHERE events.event_id = participants.event_fk AND participants.is_principal IS TRUE; Now, here's an "explain select": pgslekt=> explain select event_date, place from principals where person=2 and tag_type=2; QUERY PLAN --- Nested Loop (cost=0.00..23.15 rows=2 width=26) -> Index Scan using person_event on participants (cost=0.00..13.63 rows=3 width=4) Index Cond: (person_fk = 2) Filter: (is_principal IS TRUE) -> Index Scan using events_pkey on events (cost=0.00..3.16 rows=1 width=30) Index Cond: (events.event_id = "outer".event_fk) Filter: (tag_type_fk = 2) (7 rader) Why doesn't this SELECT use the partial index "events_born" above? Is there any way to make this happen? -- Leif Biberg Kristensen http://solumslekt.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Use of partial index
As I understand it, partial indices are generally useful when you only want to index a range of values, or if the select condition is on a different field from the one being indexed (eg: ON foo (a) WHERE b IS NOT NULL). I am just guessing here, but it sounds like 'person_fk = 2' is going to be a lot more selective (ie return less rows) than 'tag_type_fk = 2', so it's quicker to use the pkey and then filter the results. Depending on how many 'tag_type' values you have, indexing on it will not help at all. In other words, if more than a few percent of the rows have the value '2' for 'tag_type_fg', postgres will tend to favour more selective indices if you are doing a join, or a seqscan if you are doing a straight select on that value. Does that help? Dmitri PS Your query seems to be quite quick already, why don't you like this plan? > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Leif B. > Kristensen > Sent: Wednesday, October 05, 2005 11:17 AM > To: Pgsql-sql@postgresql.org > Subject: [SQL] Use of partial index > > > I'm a little confused about partial indexes. I have a couple > of tables, > like this: > > CREATE TABLE events ( > event_idINTEGER PRIMARY KEY, > tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), > place_fkINTEGER REFERENCES places (place_id), > event_date CHAR(18) NOT NULL DEFAULT '31', > sort_date DATE NOT NULL DEFAULT '40041024BC', > event_text TEXT NOT NULL DEFAULT '', > sentenceTEXT NOT NULL DEFAULT '' > ); > > To this table I have created a partial index: > > CREATE INDEX events_born > ON events (tag_type_fk) > WHERE tag_type_fk = 2; > > Another table: > > CREATE TABLE participants ( -- the TMG 'E' file > participant_id INTEGER PRIMARY KEY, > person_fk INTEGER REFERENCES persons (person_id), > event_fkINTEGER REFERENCES events (event_id), > role_type_fkINTEGER REFERENCES role_types (role_type_id), > is_principalBOOLEAN NOT NULL DEFAULT 'f', > is_primary_eventBOOLEAN NOT NULL DEFAULT 'f', > participant_noteTEXT NOT NULL DEFAULT '', > participant_nameTEXT NOT NULL DEFAULT '', > age_meanINTEGER NOT NULL DEFAULT 0, > age_deviINTEGER NOT NULL DEFAULT 0, > CONSTRAINT person_event UNIQUE (person_id, event_id) > ); > > And a view: > > CREATE OR REPLACE VIEW principals AS > SELECT > participants.person_fk AS person, > events.event_id AS event, > events.place_fk AS place, > events.event_date AS event_date, > events.sort_date AS sort_date, > events.tag_type_fk AS tag_type > FROM > events, participants > WHERE > events.event_id = participants.event_fk > AND > participants.is_principal IS TRUE; > > Now, here's an "explain select": > > pgslekt=> explain select event_date, place from principals where > person=2 and tag_type=2; >QUERY PLAN > -- > - > Nested Loop (cost=0.00..23.15 rows=2 width=26) >-> Index Scan using person_event on participants >(cost=0.00..13.63 rows=3 width=4) > Index Cond: (person_fk = 2) > Filter: (is_principal IS TRUE) >-> Index Scan using events_pkey on events >(cost=0.00..3.16 rows=1 width=30) > Index Cond: (events.event_id = "outer".event_fk) > Filter: (tag_type_fk = 2) > (7 rader) > > Why doesn't this SELECT use the partial index "events_born" above? Is > there any way to make this happen? > -- > Leif Biberg Kristensen > http://solumslekt.org/ > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Use of partial index
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > I'm a little confused about partial indexes. I have a couple of tables, > like this: > CREATE TABLE events ( > event_idINTEGER PRIMARY KEY, > tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), > place_fkINTEGER REFERENCES places (place_id), > event_date CHAR(18) NOT NULL DEFAULT '31', > sort_date DATE NOT NULL DEFAULT '40041024BC', > event_text TEXT NOT NULL DEFAULT '', > sentenceTEXT NOT NULL DEFAULT '' > ); > To this table I have created a partial index: > CREATE INDEX events_born > ON events (tag_type_fk) > WHERE tag_type_fk = 2; > ... > Now, here's an "explain select": > pgslekt=> explain select event_date, place from principals where > person=2 and tag_type=2; >QUERY PLAN > --- > Nested Loop (cost=0.00..23.15 rows=2 width=26) >-> Index Scan using person_event on participants >(cost=0.00..13.63 rows=3 width=4) > Index Cond: (person_fk = 2) > Filter: (is_principal IS TRUE) >-> Index Scan using events_pkey on events >(cost=0.00..3.16 rows=1 width=30) > Index Cond: (events.event_id = "outer".event_fk) > Filter: (tag_type_fk = 2) > (7 rader) > Why doesn't this SELECT use the partial index "events_born" above? Because the plan it did choose is better. events_born could only serve to select the rows with tag_type_fk = 2; assuming there's more than one of those rows, there would be multiple fetches needed to see if any of them have the desired event_id. With this plan it's getting at most one row, by definition (since event_id is the primary key). Had you created the partial index as CREATE INDEX events_born ON events (event_id) WHERE tag_type_fk = 2; then it would be competitive for this query, since the index could effectively handle both constraints not just one. (THe way you did define it, the actual content of the index keys is just dead weight, since they obviously must all be "2". It's often better to define the index column(s) of a partial index as some other column than the one involved in the index predicate...) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Use of partial index
On Wed, 5 Oct 2005, Leif B. Kristensen wrote: > I'm a little confused about partial indexes. I have a couple of tables, > like this: > > CREATE TABLE events ( > event_idINTEGER PRIMARY KEY, > tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), > place_fkINTEGER REFERENCES places (place_id), > event_date CHAR(18) NOT NULL DEFAULT '31', > sort_date DATE NOT NULL DEFAULT '40041024BC', > event_text TEXT NOT NULL DEFAULT '', > sentenceTEXT NOT NULL DEFAULT '' > ); > > To this table I have created a partial index: > > CREATE INDEX events_born > ON events (tag_type_fk) > WHERE tag_type_fk = 2; > > Another table: > > CREATE TABLE participants ( -- the TMG 'E' file > participant_id INTEGER PRIMARY KEY, > person_fk INTEGER REFERENCES persons (person_id), > event_fkINTEGER REFERENCES events (event_id), > role_type_fkINTEGER REFERENCES role_types (role_type_id), > is_principalBOOLEAN NOT NULL DEFAULT 'f', > is_primary_eventBOOLEAN NOT NULL DEFAULT 'f', > participant_noteTEXT NOT NULL DEFAULT '', > participant_nameTEXT NOT NULL DEFAULT '', > age_meanINTEGER NOT NULL DEFAULT 0, > age_deviINTEGER NOT NULL DEFAULT 0, > CONSTRAINT person_event UNIQUE (person_id, event_id) > ); > > And a view: > > CREATE OR REPLACE VIEW principals AS > SELECT > participants.person_fk AS person, > events.event_id AS event, > events.place_fk AS place, > events.event_date AS event_date, > events.sort_date AS sort_date, > events.tag_type_fk AS tag_type > FROM > events, participants > WHERE > events.event_id = participants.event_fk > AND > participants.is_principal IS TRUE; > > Now, here's an "explain select": > > pgslekt=> explain select event_date, place from principals where > person=2 and tag_type=2; >QUERY PLAN > --- > Nested Loop (cost=0.00..23.15 rows=2 width=26) >-> Index Scan using person_event on participants >(cost=0.00..13.63 rows=3 width=4) > Index Cond: (person_fk = 2) > Filter: (is_principal IS TRUE) >-> Index Scan using events_pkey on events >(cost=0.00..3.16 rows=1 width=30) > Index Cond: (events.event_id = "outer".event_fk) > Filter: (tag_type_fk = 2) > (7 rader) > > Why doesn't this SELECT use the partial index "events_born" above? Is > there any way to make this happen? I would think you'd want an index ON events(event_id) WHERE tag_type_fk=2 for the query given. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Use of partial index
On Wednesday 05 October 2005 18:44, you wrote: > As I understand it, partial indices are generally useful when you > only want to index a range of values, or if the select condition is > on a different field from the one being indexed (eg: ON foo (a) WHERE > b IS NOT NULL). > > I am just guessing here, but it sounds like 'person_fk = 2' is going > to be a lot more selective (ie return less rows) than 'tag_type_fk = > 2', so it's quicker to use the pkey and then filter the results. That makes a lot of sense to me. As any person will usually participate in several events, I'll estimate that the ratio between person_fk=x and tag_type_fk=y is about 1:4. > Depending on how many 'tag_type' values you have, indexing on it will > not help at all. In other words, if more than a few percent of the > rows have the value '2' for 'tag_type_fg', postgres will tend to > favour more selective indices if you are doing a join, or a seqscan > if you are doing a straight select on that value. I have only 53 different tag types, but most of them are rather rare. The most heavilyly used are birth=2, death=3, and marriage=4, and I've created similar partial indexes for the other two. In a previous MySQL project that's roughly sharing the same data model, I created a redundant table called "marriages" that speeded up the generation of a family view by a factor of four. That's why I thought that a partial index would have a similar effect here. > Does that help? Yessir, thank you very much! > Dmitri > PS Your query seems to be quite quick already, why don't you like > this plan? I didn't say that I didn't like it, - I just was a little perplexed :-) -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo-Linux/KDE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Use of partial index
On Wednesday 05 October 2005 18:49, you wrote: [Leif] > > Now, here's an "explain select": > > > > pgslekt=> explain select event_date, place from principals where > > person=2 and tag_type=2; > > QUERY PLAN > > --- > > Nested Loop (cost=0.00..23.15 rows=2 width=26) > > -> Index Scan using person_event on participants > > (cost=0.00..13.63 rows=3 width=4) > > Index Cond: (person_fk = 2) > > Filter: (is_principal IS TRUE) > > -> Index Scan using events_pkey on events > > (cost=0.00..3.16 rows=1 width=30) > > Index Cond: (events.event_id = "outer".event_fk) > > Filter: (tag_type_fk = 2) > > (7 rader) > > Because the plan it did choose is better. events_born could only > serve to select the rows with tag_type_fk = 2; assuming there's more > than one of those rows, there would be multiple fetches needed to see > if any of them have the desired event_id. With this plan it's getting > at most one row, by definition (since event_id is the primary key). > > Had you created the partial index as > > CREATE INDEX events_born > ON events (event_id) > WHERE tag_type_fk = 2; > > then it would be competitive for this query, since the index could > effectively handle both constraints not just one. (THe way you did > define it, the actual content of the index keys is just dead weight, > since they obviously must all be "2". It's often better to define > the index column(s) of a partial index as some other column than the > one involved in the index predicate...) > pgslekt=> create index events_born2 on events (event_id) where tag_type_fk=2; CREATE INDEX pgslekt=> explain select event_date, place from principals where person=2 and tag_type=2; QUERY PLAN Nested Loop (cost=0.00..22.88 rows=2 width=26) -> Index Scan using person_event on participants (cost=0.00..13.63 rows=3 width=4) Index Cond: (person_fk = 2) Filter: (is_principal IS TRUE) -> Index Scan using events_born2 on events (cost=0.00..3.07 rows=1 width=30) Index Cond: (events.event_id = "outer".event_fk) Filter: (tag_type_fk = 2) (7 rader) >From 23.15 to 22.88 ... but now at least it used my partial index, as it does a slightly better job. I'm starting to get it - I think. Thank you for your explanation. With regards to optimization, it seems that I'm still too hung up in MySQL issues. PostgreSQL seems to behave a lot more intelligently with queries. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo-Linux/KDE ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Scripting GRANT on functions
Is there any easy way to script granting privileges to a number of functions? I've got as far as the following code before realising that I'll need to pass in the arguments, and the arguments are stored as OIDs in pg_proc. Is there any easy way, such as GRANT FUNCTION OID 12345? ---CODE--- DECLARE curs REFCURSOR; funcname VARCHAR; BEGIN OPEN foo FOR SELECT proname FROM pg_proc WHERE proname LIKE 'tr\\_%' OR proname LIKE 'tt\\_%' OR proname LIKE 'v\\_%' OR proname LIKE 'vui\\_%'; FETCH curs INTO funcname; WHILE FOUND LOOP FETCH curs INTO funcname; EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser'; END LOOP; CLOSE curs; END; ---END CODE--- Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
