Re: [SQL] where to start with this procedure ?
Hi, Use the Offset and Limit in the SQL query. http://developer.postgresql.org/docs/postgres/queries-limit.html For example: SELECT select_list FROM table_expression WHERE condition Let's supose that this query returnes normaly 3 elements. SELECT select_list FROM table_expression WHERE condition LIMIT 50 OFFSET 1 This query will return 50 elements starting with the 1 elements... so the elemenst from 1 to 10050. Hope that helps. Best regards, Andy. - Original Message - From: "Andreas" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 20, 2004 3:33 AM Subject: [SQL] where to start with this procedure ? > Hi, > I know a bit of SQL but not exactly a lot so I ran into this problem. > > I have tables on the server that get joined in a view. > That's OK. > > Now I need just a couple of records say 10-100 of 30 000 which could > easily be filtered by a integer key. > As of now I have to pull the whole lot into Access and let it do the > filtering. That doesn't play nice on our network. > > Could someone kick me into the right direction where to learn stuff like: > > function grabem(x integer) recordset > ( >grabem = select * from my_view where key = x > ) > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] where to start with this procedure ?
Hi Andrei, Use the Offset and Limit in the SQL query. [...] SELECT select_list FROM table_expression WHERE condition LIMIT 50 OFFSET 1 This query will return 50 elements starting with the 1 elements... so the elemenst from 1 to 10050. That isn't the issue since I only need a specific few of the lines at all. I need a dynamic WHERE clause. It's more like this : SELECT customer_id, and, some, more, fields FROM table1 JOIN table2 JOIN table3 This results in the "huge" set. On the Access-form I only need the orders, or contact history for 1 customer. As of now I only know 2 ways to solve this. 1) Load all the tables over the net into Access and let the JOIN run locally. That way I can filter dynamically by adding a WHERE customer_id = x to the select above. 2) Let the JOINS run in a server based view and transfer the whole result into Access to fetch the few lines regarding the customer. There should be something like: 3) a) Access calls a server based function with the customer_id as parameter. b) The function calls the server based view and filters the result acording to it's parameter. c) The function sends only the actually wanted lines back to Access. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] where to start with this procedure ?
On Thu, 20 May 2004, Andreas wrote: > > Hi Andrei, > > > >Use the Offset and Limit in the SQL query. > >[...] > >SELECT select_list > >FROM table_expression > >WHERE condition > >LIMIT 50 > >OFFSET 1 > > > >This query will return 50 elements starting with the 1 elements... so > >the elemenst from 1 to 10050. > > > > > > That isn't the issue since I only need a specific few of the lines at all. > I need a dynamic WHERE clause. > > It's more like this : > SELECT customer_id, and, some, more, fields > FROM table1 > JOIN table2 >JOIN table3 > > > This results in the "huge" set. > On the Access-form I only need the orders, or contact history for 1 > customer. > > As of now I only know 2 ways to solve this. > 1) Load all the tables over the net into Access and let the JOIN run > locally. That way I can filter dynamically by adding a WHERE > customer_id = x to the select above. Why not add the where clause to the select that goes to the server? In any case, you can do a function that does the select with a condition fairly easily with a set returning function in recent versions I think. Make a composite type with the return fields (create type as (...)) then make an sql function that takes an integer returns setof that selects from table1 join table2 ... where customer_id = $1. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Replace function ...
I try to reencode postgresql db ... all is running ... in your shell : - pg_dump db -f fileout - iconv fileout i do not remember parameters but iconv converts the file. - createdb db -U unicode (for example) - pgsql db < fileout_converted We change previous year the encoding of this website : www.ape-europe.org 'iso' to 'utf8' for problems of eastern countries ... all is running ... maybe you can try ... Kornelije wrote: Hi, I need to write a function which has to replace a character with two or three other characters I have some data in the DB and when I retrieve this data, I have sorting problems because I use specific Croatian characters I know that the DB is not setup properly, but I cannot change anything now because some data could be lost So I need to change specific Croatian characters with some other standard characters in order to sort the data properly so, if anybody knows something or has such function, please, let me know thanks R.K.
[SQL] OR clause causing strange index performance
Hello,
For the following query:
SELECT *
FROM permissions p
INNER JOIN users u
ON u.id = p.id
LEFT JOIN user_list ul1
ON ul1.id = u.id
AND ul1.type = '1'
LEFT JOIN user_list ul2
ON ul2.id = u.id
AND ul2.type = '2'
INNER JOIN lists l
ON ( l.list_id1 = ul1.list_id1 AND l.list_id2 = ul1.list_id2 )
OR
( l.list_id1 = ul2.list_id1 AND l.list_id2 = ul2.list_id2 )
WHERE
p.code = '123456' AND p.type = 'User'
(lists table has ~ 500k records, users ~ 100k, permissions ~ 60k, user_list
~ 530k)
lists can be associated with 2 users via the user_list table, and are
designated by the 1 or 2, can have a user with a 1, a user with a 2 or one
of each.
I'm getting really poor performance... about 60 seconds. Explain (see
below) is showing its trying to use the pkey (list_id1,list_id2) on the
list table, but not showing an index condition.
If I get rid of the OR, and only at one of the conditions it returns very
quickly and properly set the index condition. I can't use a union because I
would end up with duplicate rows for those that have both ul type 1 and 2
I actually started off trying the query by looking at lists first, but
performance was awful since I can't narrow down the records like I can with
permissions.
I know the tables aren't really set up ideally, and I actually have to join
a few more tables to the lists table after the fact, but want to get the
base running as efficient as possible first.
Is there any way to get this query to use the correct index condition so
that it runs in a reasonable amount of time?
Thanks!
EXPLAIN with the OR
QUERY
PLAN
Nested Loop (cost=0.00..13051262.13 rows=1 width=1794)
Join Filter: ((("inner".list_id1 = "outer".list_id1) OR
("inner".list_id1 = "outer".list_id1)) AND (("inner".list_id2 =
"outer".list_id2) OR ("inner".list_id1 = "outer".list_id1)) AND
(("inner".list_id1 = "outer".list_id1) OR ("inner".list_id2 =
"outer".list_id2)) AND (("inner".list_id2 = "outer".list_id2) OR
("inner".list_id2 = "outer".list_id2)))
-> Nested Loop (cost=0.00..2654.08 rows=12 width=1087)
Join Filter: ("inner".type = '2'::character varying)
-> Nested Loop (cost=0.00..427.39 rows=12 width=1032)
Join Filter: ("inner".type = '1'::character varying)
-> Nested Loop (cost=0.00..23.82 rows=2 width=977)
-> Index Scan using permissions_pkey on permissions
p (cost=0.00..12.14 rows=2 width=476)
Index Cond: ((code = '123456'::character
varying) AND (type = 'User'::character varying))
-> Index Scan using users_pkey on users
u (cost=0.00..4.92 rows=1 width=501)
Index Cond: (u.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul1 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul1.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul2 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul2.id = "outer".id)
-> Seq Scan on lists 1 (cost=0.00..26103.61 rows=508361 width=707)
(16 rows)
Note: this example shows it trying a seq scan.. I've tried it with
enable_seqscan off, too. When I referred above to it trying to use index
scan, it was from an explain with an additional join to the lists table after:
-> Index Scan using lists_pkey on lists l (cost=0.00..1872375.82
rows=508361 width=144)
EXPLAIN without the OR
QUERY
PLAN
Nested Loop (cost=0.00..2740.09 rows=17 width=1794)
-> Nested Loop (cost=0.00..2654.08 rows=12 width=1087)
Join Filter: ("inner".type = '2'::character varying)
-> Nested Loop (cost=0.00..427.39 rows=12 width=1032)
Join Filter: ("inner".type = '1'::character varying)
-> Nested Loop (cost=0.00..23.82 rows=2 width=977)
-> Index Scan using permissions_pkey on permissions
p (cost=0.00..12.14 rows=2 width=476)
Index Cond: ((code = '123456'::character
varying) AND (type = 'User'::character varying))
-> Index Scan using users_pkey on users
u (cost=0.00..4.92 rows=1 width=501)
Index Cond: (u.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul1 (cost=0.00..159.86 rows=37 width=55)
Index Cond: (ul1.id = "outer".id)
-> Index Scan using user_list_id on user_list
ul2 (cost=0.00..159.86
[SQL] Simple view confuses PostgreSQL query planning
[I sent this message two months ago and got no answer. I'm resending
now hoping to get some feedback.]
Hi,
I'm facing a wired problem. When I left join two tables PostgreSQL is
able to do it fast using the corresponding indices, However, if I
define a simple view (to format the data) on one of the tables, the
left join does not use the indices. Is something wrong here?
The two tables:
\d regacd.profesor
Tabla "regacd.profesor"
Columna|Tipo | Modificadores
--+-+---
id | integer | not null default
nextval('regacd.profesor_sid'::text)
grupo_id | integer | not null
tipo_id | "char" | not null
cargo_id | integer | not null
académico_id | integer |
última_actualización | timestamp without time zone | default now()
Índices:
"profesor_pkey" llave primaria, btree (id)
"profesor_académico" btree ("académico_id")
"profesor_grupo" btree (grupo_id)
Restricciones de llave foránea:
"CargoTipoId" FOREIGN KEY (cargo_id, tipo_id) REFERENCES cargo(id, tipo_id)
"IdentificadorGrupoTipoId" FOREIGN KEY (grupo_id, tipo_id) REFERENCES
regacd.grupo(id, tipo_id)
"$1" FOREIGN KEY ("académico_id") REFERENCES personal(id)
Triggers:
"profesor_última_actualización" BEFORE INSERT OR UPDATE ON regacd.profesor FOR
EACH ROW EXECUTE PROCEDURE "profesor_última_actualización"()
"propaga_actualización_profesor" AFTER INSERT OR UPDATE ON regacd.profesor FOR
EACH ROW EXECUTE PROCEDURE "propaga_actualización_profesor"()
"update_datos_académico" BEFORE INSERT OR UPDATE ON regacd.profesor FOR EACH ROW
EXECUTE PROCEDURE "update_datos_académico"()
\d ordinario.horario
Tabla "ordinario.horario"
Columna|Tipo |
Modificadores
--+-+-
id | integer | not null default
nextval('ordinario.horario_sid'::text)
profesor_id | integer |
lu | boolean | not null default false
ma | boolean | not null default false
mi | boolean | not null default false
ju | boolean | not null default false
vi | boolean | not null default false
sá | boolean | not null default false
hora_inicial | time without time zone |
hora_final | time without time zone |
salón_id | integer |
nota | text|
última_actualización | timestamp without time zone | default now()
Índices:
"horario_pkey" llave primaria, btree (id)
"horario_profesor" btree (profesor_id)
Restricciones de llave foránea:
"$2" FOREIGN KEY ("salón_id") REFERENCES "salón"(id)
"$1" FOREIGN KEY (profesor_id) REFERENCES regacd.profesor(id) ON UPDATE CASCADE ON
DELETE CASCADE
Triggers:
"horario_última_actualización" BEFORE INSERT OR UPDATE ON ordinario.horario FOR
EACH ROW EXECUTE PROCEDURE "horario_última_actualización"()
"propaga_actualización_horario" AFTER INSERT OR UPDATE ON ordinario.horario FOR
EACH ROW EXECUTE PROCEDURE "propaga_actualización_horario"()
Now, a left join query of the tables leads a nice and fast plan:
explain analyze select * from regacd.profesor p left join ordinario.horario h on
(h.profesor_id = p.id) where p.grupo_id IN (129314, 129315, 129316, 129317, 129318,
129319, 129320, 129321, 129322);
QUERY PLAN
-
Nested Loop Left Join (cost=0.00..150.79 rows=18 width=78) (actual time=0.400..1.232
rows=19 loops=1)
-> Index Scan using profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo on profesor p (cost=0.00..96.50 rows=1
Re: [SQL] OR clause causing strange index performance
Sorry, I just realized that my logic for the query is flawed anyway. It won't return the proper data set I'm after. I'll have to go back to looking at the lists table first. I still guess knowing why the query below isn't as quick as expected could be useful though. At 01:32 PM 5/20/2004, Doug Y wrote: Hello, For the following query: SELECT * FROM permissions p INNER JOIN users u ON u.id = p.id LEFT JOIN user_list ul1 ON ul1.id = u.id AND ul1.type = '1' LEFT JOIN user_list ul2 ON ul2.id = u.id AND ul2.type = '2' INNER JOIN lists l ON ( l.list_id1 = ul1.list_id1 AND l.list_id2 = ul1.list_id2 ) OR ( l.list_id1 = ul2.list_id1 AND l.list_id2 = ul2.list_id2 ) WHERE p.code = '123456' AND p.type = 'User' (lists table has ~ 500k records, users ~ 100k, permissions ~ 60k, user_list ~ 530k) lists can be associated with 2 users via the user_list table, and are designated by the 1 or 2, can have a user with a 1, a user with a 2 or one of each. I'm getting really poor performance... about 60 seconds. Explain (see below) is showing its trying to use the pkey (list_id1,list_id2) on the list table, but not showing an index condition. If I get rid of the OR, and only at one of the conditions it returns very quickly and properly set the index condition. I can't use a union because I would end up with duplicate rows for those that have both ul type 1 and 2 I actually started off trying the query by looking at lists first, but performance was awful since I can't narrow down the records like I can with permissions. I know the tables aren't really set up ideally, and I actually have to join a few more tables to the lists table after the fact, but want to get the base running as efficient as possible first. Is there any way to get this query to use the correct index condition so that it runs in a reasonable amount of time? Thanks! - cut explains off - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] v7.2 triggers and foreign keys
Hi, I have two tables a and b.; b references a. I used pgaccess to create tables. and psql to create the triggers and foreign keys. a has sequential id, and primary key same column. As constraints they were implicit, and cascade was off. On delete of the of a b row, postgres also deleted the a table row. I then created a2 and b2 with action queries. and the delete acted as expected. Where am i going wrong with this? Do I need to install 7.4? Also, created tables a3 and b3, with sequential ids using pgaccess same as a and b, and the seq id's don't increment with these tables. Thanks. paul
Re: [SQL] Simple view confuses PostgreSQL query planning
Manuel Sugawara <[EMAIL PROTECTED]> writes:
> I'm facing a wired problem. When I left join two tables PostgreSQL is
> able to do it fast using the corresponding indices, However, if I
> define a simple view (to format the data) on one of the tables, the
> left join does not use the indices. Is something wrong here?
> Definición de vista:
> SELECT h.id AS horario_id, h.profesor_id, h.lu, h.ma, h.mi, h.ju, h.vi, h."sá",
> "días_atxt"(h.lu, h.ma, h.mi, h.ju, h.vi, h."sá") AS "días_txt", h.hora_inicial,
> h.hora_final,
> CASE
> WHEN h.hora_inicial IS NULL THEN ''::text
> WHEN date_part('minute'::text, h.hora_inicial) = 0::double precision
> THEN date_part('hour'::text, h.hora_inicial)::text
> ELSE (date_part('hour'::text, h.hora_inicial)::text || ':'::text) ||
> to_char(date_part('minute'::text, h.hora_inicial), 'fm00'::text)
> END AS hora_inicial_txt,
> CASE
> WHEN h.hora_final IS NULL THEN ''::text
> WHEN date_part('minute'::text, h.hora_final) = 0::double precision THEN
> date_part('hour'::text, h.hora_final)::text
> ELSE (date_part('hour'::text, h.hora_final)::text || ':'::text) ||
> to_char(date_part('minute'::text, h.hora_final), 'fm00'::text)
> END AS hora_final_txt, h."salón_id", "salón_id_atxt"(h."salón_id") AS
> "salón_txt", h.nota AS horario_nota
>FROM ordinario.horario h;
I think the issue is that the subquery isn't getting flattened, because
of this test:
/*
* If we are inside an outer join, only pull up subqueries whose
* targetlists are nullable --- otherwise substituting their tlist
* entries for upper Var references would do the wrong thing (the
* results wouldn't become NULL when they're supposed to).
*
* XXX This could be improved by generating pseudo-variables for
* such expressions; we'd have to figure out how to get the pseudo-
* variables evaluated at the right place in the modified plan
* tree. Fix it someday.
*/
if (...
(!below_outer_join || has_nullable_targetlist(subquery)))
has_nullable_targetlist() is returning false because of the CASE
expressions. Its analysis could be more detailed, but in point of fact
with this particular definition the targetlist *isn't* nullable ---
the first arm of each CASE will yield a non-null result for null input.
Get rid of the CASEs (perhaps you could wrap them into functions
declared STRICT) and the view would be flattenable.
The reason we need this is shown in this old bug report:
http://archives.postgresql.org/pgsql-bugs/2001-04/msg00223.php
If the view did get flattened then the CASE outputs would give
wrong answers --- nonnull when they should be null --- just as Victor
described for constants.
The general fix mentioned in the comment is still a long way off.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Simple view confuses PostgreSQL query planning
Tom Lane <[EMAIL PROTECTED]> writes: > Get rid of the CASEs (perhaps you could wrap them into functions > declared STRICT) and the view would be flattenable. Will do that. Thanks. Regards, Manuel. ---(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
Re: [SQL] OR clause causing strange index performance
Doug Y <[EMAIL PROTECTED]> writes: > Is there any way to get this query to use the correct index condition so > that it runs in a reasonable amount of time? I think CVS tip (7.5-to-be) would handle this better, but it's hard to be sure since you didn't provide a self-contained test case. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
