Re: [SQL] Scaler forms as function arguments
Joe Conway <[EMAIL PROTECTED]> writes:
> In 7.4 you could use an array. It would look like this:
Though note that 7.4 doesn't know how to optimize this form:
db=> explain select * from foo where foo_id in (1,2);
QUERY PLAN
-
Index Scan using foo_pkey, foo_pkey on foo (cost=0.00..6.05 rows=2 width=756)
Index Cond: ((foo_id = 1) OR (foo_id = 2))
(2 rows)
db=> explain select * from foo where foo_id = ANY (array[1,2]);
QUERY PLAN
--
Seq Scan on foo (cost=0.00..1132.82 rows=5955 width=756)
Filter: (foo_id = ANY ('{1,2}'::integer[]))
(2 rows)
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] explicit joins wrong planning
Tomasz Myrta <[EMAIL PROTECTED]> writes: > Postgres doesn't use join on these both fields and doesn't use index > scan properly. Hard to say much when you didn't actually show us the output of EXPLAIN ANALYZE. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] explicit joins wrong planning
Dnia 2003-11-28 20:18, Użytkownik Tom Lane napisał:
Hard to say much when you didn't actually show us the output of EXPLAIN
ANALYZE.
OK, Here you are:
explain analyze select *
from plany pl
join linia_trasy lt using (id_linii)
join kursy k on (k.id_trasy=lt.id_trasy and
k.event_date=pl.begindate+lt.offset)
where pl.id_planu=508;
event_date - date
begindate - date
id_linii - integer
id_trasy - integer
offset - integer
QUERY PLAN
---
Hash Join (cost=5.82..1210.68 rows=2 width=40) (actual
time=718.11..759.27 rows=5 loops=1)
Hash Cond: ("outer".id_trasy = "inner".id_trasy)
Join Filter: ("outer".event_date = ("inner".begindate + "inner".offset))
-> Seq Scan on kursy k (cost=0.00..876.07 rows=58707 width=11)
(actual time=0.02..547.65 rows=58707 loops=1)
-> Hash (cost=5.81..5.81 rows=7 width=29) (actual time=0.43..0.43
rows=0 loops=1)
-> Nested Loop (cost=0.00..5.81 rows=7 width=29) (actual
time=0.16..0.37 rows=7 loops=1)
Join Filter: ("outer".id_linii = "inner".id_linii)
-> Index Scan using plany_pkey on plany pl
(cost=0.00..4.49 rows=1 width=17) (actual time=0.09..0.11 rows=1 loops=1)
Index Cond: (id_planu = 508)
-> Seq Scan on linia_trasy lt (cost=0.00..1.14 rows=14
width=12) (actual time=0.02..0.12 rows=14 loops=1)
If I add "and k.id_trasy=lt.id_trasy" into where clause (duplicate), the
query works fine.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] explicit joins wrong planning
Tomasz Myrta <[EMAIL PROTECTED]> writes: > If I add "and k.id_trasy=lt.id_trasy" into where clause (duplicate), the > query works fine. Define "works fine", please (again, EXPLAIN ANALYZE would be a nice concrete description). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL a simple menu - plz help
>> You're in need of the "CONNECT BY" option which is ideal for >> scenarios such as yours. Unfortunately PostgreSQL doesn't have it >> yet, but I believe there is a PLSQL script (or something like this) >> which emulates the CONNECT BY behaviour and it's called "connectby()" >> or something like that. > > Thanks...Ill look into that. Here, this link should help: Search PostgreSQL - Opera http://www.postgresql.org/search.cgi?q=connectby -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to quote date value?
Thanks, I learnt to use:
SELECT CAST('1/11/2003' AS DATE) AS invoice_number;
and it does what I would expect ;-).
"Stephan Szabo" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> On Fri, 21 Nov 2003, Gaetano Mendola wrote:
>
> > nobody wrote:
> > > I have found it in documentation, it is single quote. But it does not
> > > explain why
> > >
> > > SELECT '1/11/2003' AS "InvoiceDate";
> > >
> > > returns "unknown" data type instead of "date".
>
> (I haven't seen the original message yet, so I'm replying to a reply)
> Date literals are generally written as:
> DATE '1/11/2003'
>
> PostgreSQL will try to guess what type you meant with quoted strings in
> expressions, but in the above there isn't enough context to do guess that
> you meant a date really (it should probably actually be thought of as a
> string in such cases).
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL a simple menu - plz help
> As subj says. A simple menu is defined in a table. But how to extract > it? Heres the deal: > > Table menu has these 3 columns: menuidparentmenudesc > > Ok this should be easy right? Those menu-rows that has parent=0 is > "toplevel". And so forth. An example: > > 10"Items" > 20"Standings" > 30"Test" > 42"Liverpool" > 52"AC Milan" > 61"Itemno1" > > This menu should in layout come in this order: > > Items > Itemno1 > Test > Standings > AC Milan > Liverpool > > That is: Alphabetically sorted and with the children underneath the > parents. Right? Yes - and selecting the menu and doing some gymnastics > in ASP isnt that hard. But ASP must be the language that has the > "crapiest" way of handling arrays...so a simple task is really messy. > Creating a stored procedure involes 2-3 temporary tables as I see it. > Not vey elegant either. > > So my question is: Isnt there any way of selecting this order directly > using SQL?? Some nice clean and beautiful way? You're in need of the "CONNECT BY" option which is ideal for scenarios such as yours. Unfortunately PostgreSQL doesn't have it yet, but I believe there is a PLSQL script (or something like this) which emulates the CONNECT BY behaviour and it's called "connectby()" or something like that. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Maintaining an order for a group of records
Hello group, I'm looking for a neat way to accomplish the following with PostgreSQL. In a table, I have a group of records for which I want to store an order. The order has nothing to do with the data of the records; on the contrary, the order of the records might change without the (other) data getting updated. It should be easy to rearrange the order of the records, cover add/delete of new records, etc. And of course, I should be able to retrieve the records in order. The first way I think of, is adding an extra column to the table, to contain an integer attribute "position" (+unique constraint). This makes retrieving the records in order as easy as adding "ORDER BY position" to the query. However, it can be fairly tedious to maintain from my application when the order is changed, unless... some triggers would do that automatically. For example, if an update is issued to change the position of a record to X, a trigger could check if there is already a record in this position, and if so, move this other record to position X+1, and so one. To declare what I want a little more: if there are N records in the group, after every UPDATE / DELETE / INSERT statement, triggers should make sure there is an order assigned through "position" values 1 up to N, giving preference to: 1) The new position given in the UPDATE / INSERT statement 2) The old position that was already present in the table before the statement was issued As I'm quite new to PL/pgSQL and trigger programming, I would like to know if anyone has ever seen something like this implemented. I found a start at http://prosodic.ods.org/?v=blog&d=2003.11.07&item=3, but I'd like to do more than that. Moreover, I'd like to know better or easier ways to do this, if they exist. Thanks! Bruno. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to quote date value?
If you want an explicit date, then cast it like this: SELECT '1/11/2003'::date AS "InvoiceDate"; Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Stephan Szabo > Sent: Friday, November 21, 2003 2:04 PM > To: [EMAIL PROTECTED] > Subject: Re: [SQL] How to quote date value? > > > > On Fri, 21 Nov 2003, Gaetano Mendola wrote: > > > nobody wrote: > > > I have found it in documentation, it is single quote. But > it does not > > > explain why > > > > > > SELECT '1/11/2003' AS "InvoiceDate"; > > > > > > returns "unknown" data type instead of "date". > > (I haven't seen the original message yet, so I'm replying to a reply) > Date literals are generally written as: > DATE '1/11/2003' > > PostgreSQL will try to guess what type you meant with quoted > strings in > expressions, but in the above there isn't enough context to > do guess that > you meant a date really (it should probably actually be > thought of as a > string in such cases). > > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Expressional Indexes
[sNip] >> bearing in mind that this index can be used with queries that contain >> WHERE conditions like "create_date >= some-date-constant". The planner >> must be able to convince itself that the right-hand side of the WHERE >> condition is >= the cutoff in the index's predicate condition. Since >> the planner is not very bright, both items had better be simple DATE >> constants, or it won't be able to figure it out ... > > Note that if you're just doing this to speed up regular queries where > you have create_date in some small range, then you'll likely not see > much of an increase. Mainly you'll just save space. Saving space is not my objective for this particular problem, but of course it's definitely good news as I strive to optimize from every angle whenever possible. What I'm aiming for is to get an increase, no matter how small, because the volume is expected to be extremely heavy duty (unfortunately I can't discuss too many details of the project due to agreements I've made with others), so even if the end result seems insignificant from the perspective of a single query, the advantages become obvious when the queries are performed repeatedly simultaneously for wide variety of massive numbers of clients. > What can be interesting is to create a partial index like this but over > a second unrelated column. Something like: > > CREATE INDEX my_dec_03_index on my_table (userid) > WHERE (create_date >= date '2003-11-02'); > > Then you can do queries like > > SELECT * FROM my_table WHERE userid = ? AND create_date >= date > '2003-11-02' > > And it'll be able to efficiently pull out just those records, even if > there are thousands more records that are older than 2003-11-02. What a fascinating trick. I could also use dummy data instead of "userid" (which has obvious functionality as implied by its name), or even data that the applications can even specify first because they'll be able to determine things on the client-side that will make index selection more appropriate. I haven't checked into this yet, but is there a way to specify which index PostgreSQL use as a parameter in a SELECT? > This avoids having to create a two-column index with a low-selectivity > column like "month". Thanks, both of you. I've got some re-thinking to do for this project I'm working on (since it's a low priority project at the moment, I've got plenty of time to re-work the plan over and over again). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How to quote date value?
What is correct character to quote date values in SELECT. Is is single quote? When I do: SELECT '1/11/2003' AS "InvoiceDate"; the returned value is of unknown type (not a date). Then I tried: SELECT 1/11/2003 AS "InvoiceDate"; and the InvoiceDate was set to zero (I guess the engine divided the numbers in date). Then I tried: SELECT 1-11-2003 AS "InvoiceDate"; and the numbers in date value got substracted. Using PostgreSQL 7.4 and PgAdmin 3. Any insight is welcome. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to quote date value?
I have found it in documentation, it is single quote. But it does not explain why SELECT '1/11/2003' AS "InvoiceDate"; returns "unknown" data type instead of "date". "nobody" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > What is correct character to quote date values in SELECT. Is is single > quote? > > When I do: > > SELECT '1/11/2003' AS "InvoiceDate"; > > the returned value is of unknown type (not a date). > > Then I tried: > > SELECT 1/11/2003 AS "InvoiceDate"; > > and the InvoiceDate was set to zero (I guess the engine divided the numbers > in date). > > Then I tried: > > SELECT 1-11-2003 AS "InvoiceDate"; > > and the numbers in date value got substracted. > > Using PostgreSQL 7.4 and PgAdmin 3. > > Any insight is welcome. > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] OFFSET and subselects
I recently noticed a behaviour which seems quite suboptimal - I am working on a "mart" type application, which in practice means I end up with queries which have a few filters on several central tables, and then a few dozen subselects for other info (which seems to perform better than several dozen equivalent LEFT JOINs do). I am running in trouble with pagination here, somehow (rather naively) I assumed that when doing a LIMIT and OFFSET, the subselects on the records before the OFFSET would not be performed, which quite apparently is not the case. So, LIMIT 50 OFFSET 0 takes 100ms to run, LIMIT 50 OFFSET 50 takes 200ms, LIMIT 50 OFFSET 100 takes 300ms; and so forth, this really becomes unacceptable after a few pages. I was wondering how I migh improve the situation? Two possibilities come to mind: a) run the query without the subselects and store the LIMIT 50 in a temp table, doing a select all from that with the subselects as a separate query or b) similar, but run the queries from the subselects as separate queries, assembling everything in the app. Is one better than the other in any way? Are there better ways to deal with this? Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] rules and return values question
We have an interesting case where we want to use Postgres both as a database
and a front end to a proprietary database. For the latter, we wrote
functions that access the proprietary database. Then we defined views on the
proprietary database and wrote rules for insert/update/delete on those views
using the functions. The problem is that we cannot find a way to return
reasonable error values from the access functions. The rules always return
the result of the last query executed and 0 if the query is not of the same
type as the original query in the rule.
CREATE FUNCTION old_db_view_func(int, int, int) RETURNS int AS '...',
'old_db_view_func' LANGUAGE 'C' VOLATILE STRICT; // this accesses the
proprietary database and retrieves all rows
CREATE FUNCTION old_db_del_func(int) RETURNS int AS '...',
'old_db_del_func', LANGUAGE 'C' VOLATILE STRICT; // this accesses the
proprietary database and deletes a row
CREATE VIEW old_db_view AS SELECT t.a, t.b, t.c FROM old_db_view_func() AS
t;
CREATE TABLE old_db_log (op text, a int, b, int, c int, res int);
CREATE RULE old_db_del AS ON DELETE TO old_db_view DO INSTEAD INSERT INTO
old_db_log VALUES ('delete', OLD.a, OLD.b, OLD.c, old_db_del_func(OLD.a));
Now when you execute: DELETE FROM old_db_view WHERE a=1; you always get 0 as
a result whether there is a row with a=1 or not, since the last query is an
INSERT (into old_db_log) and is not of the same type as the original query
(DELETE). And even if you do an INSERT INTO old_db_view VALUES (1,2,3);
where there already exists a row with a =1 and the function returns an
error, the rule returns 1.
We do not really need the old_db_log but you cannot invoke functions
directly from the rules. You need to give a query. We could have dummy
tables but it introduces too much overhead. In any case, we need the return
value (or an exception) from the function but there does not seem a way to
return it.
Anybody had a similar requirement before?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL a simple menu - plz help
>> Here, this link should help: >> >> Search PostgreSQL - Opera >> http://www.postgresql.org/search.cgi?q=connectby > > Thanks...rtfm I know :) RTFM? Oh, yeah, that's right -- Read The /FUNNY/ Manual! =D -- Randolf Richardson - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Stored procedures and relations
Hi everybody, I have a stored procedure that drops table A and then creates table A again. The table A has no constraints on any columns. When I execute the stored procedure by saying: SELECT sp_myproc(); It gives me an error saying : Relation 68428 does not exist. When I remove the CREATE FUNCTION and the stuff used to make it a stored procedure, and run the commands in the procedure, it works correctly. This behaviour is observed on all stored procedures on PG-SQL. I would really appreciate any help or interpretation of this error. By the way, the same stored procedure (with the syntax changed of course) works perfectly on MS SQL Server. Thanks in advance. Regards. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Unsigned numbers
Hello Friends, I have the following questions. I have a lot of fields in my database that are int or float. The problem is that I need them to be only positive (unsigned) and the users try to insert sometimes negative. I know that I can make this with CHECK CONSTRAINT but for some reasons this is not a good solution for me. The reason is that when I have a check constraint on a certain field and someone try to insert negative number the error message contains the name of the constraint and the name of the table but not the name of the field. So on higher level I can't send a proper message to the user. Are there any unsigned data types like unsigned int or unsigned float. Can I make a domain or something like that (How exactly to add constraint to domain). I am using v7.3.2. Thanks! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Bug: Sequence generator insert
Dear Sir,
I found a sequence generator software bug associated with duplicate key
inserts that may be of interest to you.
I first created a table with a primary key based on a sequence generator.
The following script provides an example.
CREATE SEQUENCE "id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1;
REVOKE ALL on "id_seq" from PUBLIC;
GRANT ALL on "id_seq" to "administrator";
CREATE TABLE "example" (
"id" integer DEFAULT nextval('id_seq'::text) NOT NULL,
"colum_name" character varying(15) NOT NULL,
Constraint "ex_pkey" Primary Key ("id")
);
REVOKE ALL on "example" from PUBLIC;
GRANT ALL on "example" to "administrator";
When I tried to insert a record into the table where a duplicate record
already exists, predictably, the insert fails and the following error is
generated:
'ERROR: Cannot insert a duplicate key into unique index ex_pkey'
However, even though the new record failed to be inserted into the table,
the sequence generator was still updated.
The sequence generator should only be updated if the record is successfully
inserted into the table, and should not be updated if the insert fails.
Sincerely yours,
Colin Burr
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Do update permissions require select permissions
I have run across a problem while creating a database with row level permission checking via views and triggers. The just of which is update does not work for a user unless they also have select permissions. Select permissions are handled with a view, while insert, update and delete permissions are handled via triggers. The simplest table which demonstrates the problem is: create table data_t (id int4, stuff text); grant insert,update,delete on data_t to public; As the table creator issue the query: insert into data_t values (1, 'Some stuff'); Now as some other user one can do: update data set stuff = 'other stuff'; And it works okay. But the following fails: update data set stuff = 'yet other stuff' where id = 1; Why is this? The reason that I need to not let the user have select on the table is that they are only allowed to select certian rows and there is a view that takes care of this. For insert update delete there are trigger functions. I know that rules on the view would handle the problem but I am using inheretence and the query plans grow to over 270 rows when rules on views on base tables are put together. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] functional index and the planner (v7.4)
I just installed v7.4 and restored a database from v7.3.4. I have an
index based on a function that the planner is using on the old version,
but doing seq scans on left joins in the new version. I have run
analyze on the table post restore. the query returns in less than 1
second on version 7.3.4 and takes over 10 seconds on version 7.4. Any
help will be appreciated.
Roger Ging
Query:
SELECT L.row_id FROM music.logfile L LEFT JOIN music.program P ON
music.fn_mri_id_no_program(P.mri_id_no) = L.program_id
WHERE L.station = UPPER('kabc')::VARCHAR
AND L.air_date = '04/12/2002'::TIMESTAMP
AND P.cutoff_date IS NULL
ORDER BY L.chron_start,L.chron_end;
planner results on 7.4:
Sort (cost=17595.99..17608.23 rows=4894 width=12)
Sort Key: l.chron_start, l.chron_end
-> Merge Left Join (cost=17135.92..17296.07 rows=4894 width=12)
Merge Cond: ("outer"."?column5?" = "inner"."?column3?")
Filter: ("inner".cutoff_date IS NULL)
-> Sort (cost=1681.69..1682.73 rows=414 width=21)
Sort Key: (l.program_id)::text
-> Index Scan using idx_logfile_station_air_date on
logfile l (cost=0.00..1663.70 rows=414 width=21)
Index Cond: (((station)::text = 'KABC'::text) AND
(air_date = '2002-04-12 00:00:00'::timestamp without time zone))
-> Sort (cost=15454.22..15465.06 rows=4335 width=20)
Sort Key: (music.fn_mri_id_no_program(p.mri_id_no))::text
-> Seq Scan on program p (cost=0.00..15192.35
rows=4335 width=20)
planner results on 7.3.4:
Sort (cost=55765.51..55768.33 rows=1127 width=41)
Sort Key: l.chron_start, l.chron_end
-> Nested Loop (cost=0.00..55708.36 rows=1127 width=41)
Filter: ("inner".cutoff_date IS NULL)
-> Index Scan using idx_logfile_station_air_date on logfile l
(cost=0.00..71.34 rows=17 width=21)
Index Cond: ((station = 'KABC'::character varying) AND
(air_date = '2002-04-12 00:00:00'::timestamp without time zone))
-> Index Scan using idx_program_mri_id_no_program on program
p (cost=0.00..3209.16 rows=870 width=20)
Index Cond: (music.fn_mri_id_no_program(p.mri_id_no) =
"outer".program_id)
table "Program" details:
Column |Type | Modifiers
+-+---
record_id | integer |
title | character varying(40) |
mri_id_no | character varying(8)|
ascap_cat | character varying(1)|
ascap_mult | numeric(5,3)|
ascap_prod | character varying(10) |
npa_ind| character varying(3)|
non_inc_in | character varying(1)|
as_pr_su | character varying(1)|
as_1st_run | character varying(1)|
as_cue_st | character varying(1)|
bmi_cat| character varying(2)|
bmi_mult | numeric(6,2)|
bmi_prod | character varying(7)|
year | integer |
prog_type | character varying(1)|
total_ep | integer |
last_epis | character varying(3)|
syndicator | character varying(6)|
station| character varying(4)|
syn_loc| character varying(1)|
spdb_ver | character varying(4)|
as_filed | character varying(4)|
bmidb_ver | character varying(4)|
cutoff_date| timestamp without time zone |
effective_date | timestamp without time zone |
program_id | character varying(5)|
Indexes:
"idx_program_mri_id_no" btree (mri_id_no)
"idx_program_mri_id_no_program" btree
(music.fn_mri_id_no_program(mri_id_no))
"idx_program_program_id" btree (program_id)
"program_mri_id_no" btree (mri_id_no)
"program_oid" btree (oid)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] explicit joins wrong planning
Dnia 2003-11-28 20:52, Użytkownik Tom Lane napisał:
Tomasz Myrta <[EMAIL PROTECTED]> writes:
If I add "and k.id_trasy=lt.id_trasy" into where clause (duplicate), the
query works fine.
Define "works fine", please (again, EXPLAIN ANALYZE would be a nice
concrete description).
Sorry, I thought the wrong case would be enough.
In first case (the worse one) I had:
Total runtime: 678.31 msec
After my changes I got:
explain analyze select *
from plany pl
join linia_trasy lt using (id_linii)
join kursy k on (k.event_date=pl.begindate+lt.offset and
k.id_trasy=lt.id_trasy)
where pl.id_planu=508
and k.id_trasy=lt.id_trasy;
^^^
QUERY PLAN
Nested Loop (cost=0.00..94.43 rows=2 width=40) (actual
time=2.97..77.55 rows=5 loops=1)
Join Filter: ("inner".event_date = ("outer".begindate + "outer".offset))
-> Nested Loop (cost=0.00..5.81 rows=7 width=29) (actual
time=0.15..0.41 rows=7 loops=1)
Join Filter: ("outer".id_linii = "inner".id_linii)
-> Index Scan using plany_pkey on plany pl (cost=0.00..4.49
rows=1 width=17) (actual time=0.09..0.10 rows=1 loops=1)
Index Cond: (id_planu = 508)
-> Seq Scan on linia_trasy lt (cost=0.00..1.14 rows=14
width=12) (actual time=0.02..0.15 rows=14 loops=1)
-> Index Scan using kursy_pkey on kursy k (cost=0.00..7.62
rows=288 width=11) (actual time=0.05..8.01 rows=533 loops=7)
Index Cond: ((k.id_trasy = "outer".id_trasy) AND (k.id_trasy =
"outer".id_trasy))
Total runtime: 78.01 msec
It's much better now (10x faster), but I've just found this plan still
isn't as I want to have. I wish I could have index usage on both fields,
it means:
Index Cond: ((k.id_trasy = "outer".id_trasy) AND (("inner".event_date =
("outer".begindate + "outer".offset)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] rules and return values question
> tables but it introduces too much overhead. In any case, we need the return > value (or an exception) from the function but there does not seem a way to > return it. Can't you use erreport() to return an exception message? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Bug: Sequence generator insert
On Tue, Nov 25, 2003 at 18:37:41 -0500, "Burr, Colin" <[EMAIL PROTECTED]> wrote: > > However, even though the new record failed to be inserted into the table, > the sequence generator was still updated. > > The sequence generator should only be updated if the record is successfully > inserted into the table, and should not be updated if the insert fails. That isn't how sequences work. If you need a sequence of numbers without gaps then you need to use something else. Note that the something else will need to have a way to let only one transaction at a time update the counter which may be a performance problem. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] explicit joins wrong planning
Tomasz Myrta <[EMAIL PROTECTED]> writes:
> It's much better now (10x faster), but I've just found this plan still
> isn't as I want to have. I wish I could have index usage on both fields,
FWIW, I see this plan in 7.4 using your original test case:
Nested Loop (cost=22.51..71.79 rows=1 width=56)
-> Hash Join (cost=22.51..47.56 rows=5 width=48)
Hash Cond: ("outer".groupid = "inner".groupid)
-> Seq Scan on offsets o (cost=0.00..20.00 rows=1000 width=12)
-> Hash (cost=22.50..22.50 rows=5 width=40)
-> Seq Scan on groups g (cost=0.00..22.50 rows=5 width=40)
Filter: ((name)::text = 'some_name'::text)
-> Index Scan using events_pkey on events e (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((e.offset_id = "outer".offset_id) AND (e.event_date =
("outer".begindate + "outer".offset_value)))
I believe the difficulty in 7.3 is because begindate and offset_value
come from different relations, and specifically from different relations
than the first index condition uses. This was fixed here:
2002-11-24 16:52 tgl
* src/: backend/nodes/copyfuncs.c, backend/nodes/equalfuncs.c,
backend/nodes/list.c, backend/nodes/outfuncs.c,
backend/nodes/readfuncs.c, backend/optimizer/path/indxpath.c,
backend/optimizer/path/joinpath.c,
backend/optimizer/path/orindxpath.c,
backend/optimizer/path/tidpath.c,
backend/optimizer/plan/initsplan.c,
backend/optimizer/util/pathnode.c,
backend/optimizer/util/plancat.c, backend/optimizer/util/relnode.c,
backend/optimizer/util/restrictinfo.c, include/nodes/nodes.h,
include/nodes/pg_list.h, include/nodes/relation.h,
include/optimizer/paths.h, include/optimizer/restrictinfo.h:
Restructure planning of nestloop inner indexscans so that the set
of usable joinclauses is determined accurately for each join.
Formerly, the code only considered joinclauses that used all of the
rels from the outer side of the join; thus for example FROM (a
CROSS JOIN b) JOIN c ON (c.f1 = a.x AND c.f2 = b.y) could not
exploit a two-column index on c(f1,f2), since neither of the qual
clauses would be in the joininfo list it looked in. The new code
does this correctly, and also is able to eliminate redundant
clauses, thus fixing the problem noted 24-Oct-02 by Hans-Jürgen
Schönig.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Do update permissions require select permissions
[EMAIL PROTECTED] (Chris Piker) writes: > Now as some other user one can do: >update data set stuff = 'other stuff'; > And it works okay. But the following fails: >update data set stuff = 'yet other stuff' where id = 1; > Why is this? Because the latter requires reading, not only writing, the table. One way to look at it is that if we didn't restrict that, then a person having only UPDATE rights could nonetheless extract information from the table. For example consider update data set stuff = stuff where id = 42; This allows the user to determine whether id 42 exists in the table (by noting the returned UPDATE count). If you had not given that user SELECT rights, presumably you don't really want him to be able to find that out. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] explicit joins wrong planning
Dnia 2003-11-28 22:57, Użytkownik Tom Lane napisał: Restructure planning of nestloop inner indexscans so that the set of usable joinclauses is determined accurately for each join. Formerly, the code only considered joinclauses that used all of the rels from the outer side of the join; thus for example FROM (a CROSS JOIN b) JOIN c ON (c.f1 = a.x AND c.f2 = b.y) could not exploit a two-column index on c(f1,f2), since neither of the qual clauses would be in the joininfo list it looked in. The new code does this correctly, and also is able to eliminate redundant clauses, thus fixing the problem noted 24-Oct-02 by Hans-Jürgen Schönig. Yes, this is a description of my problem. Thanks a lot. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Stored procedures and relations
On Fri, 28 Nov 2003, FET wrote: > Hi everybody, > I have a stored procedure that drops table A and then creates table A > again. The table A has no constraints on any columns. When I execute > the stored procedure by saying: SELECT sp_myproc(); It gives me an > error saying : Relation 68428 does not exist. > > When I remove the CREATE FUNCTION and the stuff used to make it a > stored procedure, and run the commands in the procedure, it works > correctly. > > This behaviour is observed on all stored procedures on PG-SQL. I would > really appreciate any help or interpretation of this error. By the > way, the same stored procedure (with the syntax changed of course) > works perfectly on MS SQL Server. You haven't given too many details about what your function does, but, in general if you're accessing a table whose structure is being dynamically modified during/between calls, you'll currently want to be using EXECUTE on queries relating to that table. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Infinite loop crashes server
hi all, i am using PostgreSQL 7.3.2 on redhat linux 9. there is problem when executing pl/pg sql functions. if the function enter an infinite loop. the server is hanged. cannot cancel the query. the linux itself is hanged.i cannot kill postgres process. i have to reboot the machine manually.. waited for a long time for completion. but no result . when ever there is an infinite loop happened the system have to be restarted. unfortunately it happened many time during development of the pl/pg sql function. is it a bug in postgres. or is it configuration problem? when the system is hanged disk access is 100%. the harddisk read indicator does not stop. is there a solution for this problem.. pls help regards jinujose Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: [SQL] OFFSET and subselects
[EMAIL PROTECTED] (Dmitri Bichko) writes: > I am running in trouble with pagination here, somehow (rather naively) I > assumed that when doing a LIMIT and OFFSET, the subselects on the records > before the OFFSET would not be performed, which quite apparently is not the > case. So, LIMIT 50 OFFSET 0 takes 100ms to run, LIMIT 50 OFFSET 50 takes > 200ms, LIMIT 50 OFFSET 100 takes 300ms; and so forth, this really becomes > unacceptable after a few pages. If you don't need any of the results of the subqueries in your WHERE clause then you can do this by introducing a view in your query like: SELECT *, (SELECT ...) AS sub_1, (SELECT ...) AS sub_2, (SELECT ...) AS sub_3 FROM ( SELECT x,y,z FROM ... WHERE ... ) LIMIT 50 OFFSET 50 If you do use the results of the subqueries in your where clause or order by clause then, well, you're SOL. Since the OFFSET and LIMIT clauses only kick in after the where clause restrictions are taken into account. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
