[HACKERS] Prefered Types
Hello, i have an old system where used implicit casting float-integer numeric-float numeric-integer I want define implicit casts, but postgresql don`t know cast priority now postgresql have PREFERRED flag, but only flag I can`t define prefer level like Integer=0 Numeric=1 Float=2 Maybe text = 2 or 3 and other to define My prefer cast more detail than just flag i understand what it more dificult tuning, but more flexible now i can only create duplicate operators like numeric+integer, integer+numeric, integernumeric and many other What can i do? Can i wait for prefer flag changed to prefer level? -- С уважением, Зотов Роман Владимирович руководитель Отдела разработки ЗАО НПО Консультант г.Иваново, ул. Палехская, д. 10 тел./факс: (4932) 41-01-21 mailto: zo...@oe-it.ru -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] join functions
Hello, Hackers! We have a project developed at Interbase and Firebird. Now we try use PostgreSQL and have some problem Why doesn`t work this query? select table1.field1, func1.field2 from table1 left outer join func1(table1.field1) on true where func1.field3 in (20, 100); If i have other than LEFT OUTER JOIN I can understand why ERROR: invalid reference to FROM-clause entry for table table1 but why here? for each row of table1 just evaluate func1(table1.field1) To reproduce exec this script: drop table if exists table1; create table table1(field1 integer); create or replace function func1(inputparam integer) returns table(field1 integer, field2 integer, field3 integer) as $BODY$ begin field1 := inputparam * 2; field2 := inputparam * 3; field3 := inputparam * 4; return next; inputparam := inputparam * inputparam; field1 := inputparam * 2; field2 := inputparam * 3; field3 := inputparam * 4; return next; end; $BODY$ LANGUAGE plpgsql VOLATILE; insert into table1 values(5); --select table1.field1, func1.field2 from table1 left outer join func1(table1.field1) on true where func1.field3 in (20, 100); select table1.field1, func1.field2 from table1 left outer join func1(5) on true where func1.field3 in (20, 100); Please help resolve this problem! -- С уважением, Зотов Роман Владимирович руководитель Отдела инструментария ЗАО НПО Консультант г.Иваново, ул. Палехская, д. 10 тел./факс: (4932) 41-01-21 mailto: zo...@oe-it.ru
Re: [HACKERS] join functions
07.01.2011 13:01, Nicolas Barbier пишет: That seems like a use case for LATERAL, which is not supported yet. Some recent discussion seems to be URL:http://archives.postgresql.org/pgsql-hackers/2009-09/msg00292.php. Nicolas Thank you for your answer. Sorry, what i don`t find it myself. But I can`t see when it will be done? If nobody can do this (maybe no time) what i can do to help?! I know C - Language only to read code. (I`m Delphi-programmer) and this is not that feature what i can do myself. I can try, but... It`s look like difficult. -- С уважением, Зотов Роман Владимирович руководитель Отдела инструментария ЗАО НПО Консультант г.Иваново, ул. Палехская, д. 10 тел./факс: (4932) 41-01-21 mailto: zo...@oe-it.ru -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: **[SPAM]*(8.2)** Re: [HACKERS] Query optimization problem
27.07.2010 21:37, Tom Lane пишет: Right. Because of the OR, it is *not* possible to conclude that d2.basedon is always equal to 234409763, which is the implication of putting them into an equivalence class. In the example, we do have d1.id and d2.basedon grouped in an equivalence class. So in principle you could substitute d1.id into the WHERE clause in place of d2.basedon, once you'd checked that it was being used with an operator that's compatible with the specific equivalence class (ie it's in one of the eclass's opfamilies, I think). The problem is to recognize that such a rewrite would be a win --- it could just as easily be a big loss. Even if we understood how to direct the rewriting process, I'm really dubious that it would win often enough to justify the added planning time. The particular problem here seems narrow enough that solving it on the client side is probably a whole lot easier and cheaper than trying to get the planner to do it. regards, tom lane So sorry, Tom. As I can understand you. You wouldn`t do something about it. I think, what this problem can show class of optimization problems. This query: *SLOW* SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (*d1.ID=234409763* and *d2.BasedOn=d1.id* ) OR (d2.ID=234409763); *FAST* SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (*d1.ID=234409763* and *d2.BasedOn=234409763* ) OR (d2.ID=234409763); If i use constant obvious, it works use fast plan. I think query optimizer can do this. I hope you do something to make this query faster/ Thank You. -- С уважением, Зотов Роман Владимирович руководитель Отдела инструментария ЗАО НПО Консультант г.Иваново, ул. Палехская, д. 10 тел./факс: (4932) 41-01-21 mailto: zo...@oe-it.ru
Re: [HACKERS] Query optimization problem
20.07.2010 18:31, Robert Haas: According to the EXPLAIN ANALYZE output, your slow query is executing in 0.007 ms, and your fast query is executing in 0.026 ms (i.e. not as quickly as the slow query). Since you mention that it takes 7 s further down, I suspect this is not the real EXPLAIN ANALYZE output on the real data that you're having a problem with. You might have better luck if you post the actual EXPLAIN ANALYZE output here. Incidentally, sorry for not responding sooner to your private email - I was on vacation last week. But please do keep all replies on-list so that everyone can comment. All that having been said, I think the issue here is that the query planner isn't inferring that d1.ID=some constant implies d2.ID=some constant, even though there's a join clause d1.ID=d2.ID. I'm not really sure why it isn't doing that... I suspect Tom Lane is the only person who can comment intelligently on that, and he's away this week (but if anyone else has an idea, feel free to jump in...). Yes, I have a mistake when EXPLAIN ANALYZE without data.. It create another plan, because seq scan were faster. Now I send results on real data (1 million rows) *Slow Query:* - test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d1.ID=234409763 test(# ) OR (d2.ID=234409763); QUERY PLAN -- Merge Join (cost=.72..53967.30 rows=1 width=8) (actual time=6697.847..6697.847 rows=0 loops=1) Output: d1.id, d2.id Merge Cond: (d1.id = d2.basedon) Join Filter: ((d1.id = 234409763) OR (d2.id = 234409763)) - Index Scan using id_pk on public.docprimary d1 (cost=0.00..37224.48 rows=1076842 width=4) (actual time=0.016..3184.474 rows=1076795 loops=1) Output: d1.id, d1.basedon - Index Scan using basedon_idx on public.docprimary d2 (cost=0.00..46245.14 rows=1076842 width=8) (actual time=0.011..1861.570 rows=235362 loops=1) Output: d2.id, d2.basedon Total runtime: 6697.968 ms (9 rows) --- *Fast Query:* -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d1.ID=234409763 and d2.BasedOn=234409763 test(# ) OR (d2.ID=234409763); QUERY PLAN - Nested Loop (cost=9.01..422.70 rows=1 width=8) (actual time=0.145..0.145 rows=0 loops=1) Output: d1.id, d2.id Join Filter: (((d1.id = 234409763) AND (d2.basedon = 234409763)) OR (d2.id = 234409763)) - Bitmap Heap Scan on public.docprimary d2 (cost=9.01..136.90 rows=34 width=8) (actual time=0.141..0.141 rows=0 loops=1) Output: d2.id, d2.basedon Recheck Cond: ((d2.basedon = 234409763) OR (d2.id = 234409763)) - BitmapOr (cost=9.01..9.01 rows=34 width=0) (actual time=0.136..0.136 rows=0 loops=1) - Bitmap Index Scan on basedon_idx (cost=0.00..4.62 rows=33 width=0) (actual time=0.078..0.078 rows=0 loops=1) Index Cond: (d2.basedon = 234409763) - Bitmap Index Scan on id_pk (cost=0.00..4.38 rows=1 width=0) (actual time=0.051..0.051 rows=0 loops=1) Index Cond: (d2.id = 234409763) - Index Scan using id_pk on public.docprimary d1 (cost=0.00..8.39 rows=1 width=4) (never executed) Output: d1.id, d1.basedon Index Cond: (d1.id = d2.basedon) Total runtime: 0.233 ms (15 rows) -- I use another fast query: SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (*d1.ID=234409763 and d2.BasedOn=234409763*) OR (d2.ID=234409763) Bolded part of query was d2.BasedOn=234409763 I replace it because it can help find way to optimize it automaticaly So sorry, but i can`t give programmer to do something in Postgres, because we don`t use it now as supported DB, we think about it and do some tests. It`s very hard and slow task (support another DB, now we use FireBird, and plan use another DB, and look for Postgres and MSSQL, maybe support it both as free and commercial DB solution) And in our department only 4 (with me) programmers who can programm on System Level, and only one of us (doesn`t me) know C/C++ We all programming on Delphi... If we choose Postgres as free DB platform then I can think about give programmers for Postgress development. In so large letters my
[HACKERS] Query optimization problem
*i wrote to pgsql-b...@postgresql.org they tell me write to pgsql-performa...@postgresql.org they tell me write here* *I don`t whant know how optimize query myself (i know it), and i think it must do planner.* I have a query: SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d1.ID=234409763) or (d2.ID=234409763) i think what QO(Query Optimizer) can make it faster (now it seq scan and on million records works 7 sec) This Query very fast (use indexes) and easy make from first query SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) Next plans created on table without million rows data don`t look at exec time -- Slow Query -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d1.ID=234409763) or (d2.ID=234409763); QUERY PLAN Hash Join (cost=58.15..132.35 rows=2 width=8) (actual time=0.007..0.007 rows=0 loops=1) Output: d1.id, d2.id Hash Cond: (d2.basedon = d1.id) Join Filter: ((d1.id = 234409763) OR (d2.id = 234409763)) - Seq Scan on public.docprimary d2 (cost=0.00..31.40 rows=2140 width=8) (actual time=0.002..0.002 rows=0 loops=1) Output: d2.id, d2.basedon - Hash (cost=31.40..31.40 rows=2140 width=4) (never executed) Output: d1.id - Seq Scan on public.docprimary d1 (cost=0.00..31.40 rows=2140 width=4) (never executed) Output: d1.id -- Fast Query -- test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, d2.ID test-# FROM DocPrimary d1 test-# JOIN DocPrimary d2 ON d2.BasedOn=d1.ID test-# WHERE (d2.BasedOn=234409763) or (d2.ID=234409763); QUERY PLAN - Nested Loop (cost=8.60..58.67 rows=12 width=8) (actual time=0.026..0.026 rows=0 loops=1) Output: d1.id, d2.id - Bitmap Heap Scan on public.docprimary d2 (cost=8.60..19.31 rows=12 width=8) (actual time=0.023..0.023 rows=0 loops=1) Output: d2.id, d2.basedon Recheck Cond: ((d2.basedon = 234409763) OR (d2.id = 234409763)) - BitmapOr (cost=8.60..8.60 rows=12 width=0) (actual time=0.018..0.018 rows=0 loops=1) - Bitmap Index Scan on basedon_idx (cost=0.00..4.33 rows=11 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (d2.basedon = 234409763) - Bitmap Index Scan on id_pk (cost=0.00..4.26 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (d2.id = 234409763) - Index Scan using id_pk on public.docprimary d1 (cost=0.00..3.27 rows=1 width=4) (never executed) Output: d1.id, d1.basedon Index Cond: (d1.id = d2.basedon) PGver: PostgreSQL 9.0b x86 OS: Win7 x64 - Create table query: - CREATE TABLE docprimary ( id integer NOT NULL, basedon integer, CONSTRAINT id_pk PRIMARY KEY (id) ); CREATE INDEX basedon_idx ON docprimary USING btree (basedon); -- С уважением, Зотов Роман Владимирович руководитель Отдела инструментария ЗАО НПО Консультант г.Иваново, ул. Палехская, д. 10 тел./факс: (4932) 41-01-21 mailto: zo...@oe-it.ru
