Re: [SQL] full join in view
>
> Can you add some sql examples - table & index definition,
> view definition?
> If your view doesn't contain other views or sub-selects,
> postgres should
> use indexes.
> Tomasz Myrta
>
You are right. After disabling seq_scan, it uses indexes just as you described.
Unfortunately my view happens to use subquery:
CREATE OR REPLACE VIEW v_tegelikud_kulud AS
SELECT
*,
COALESCE(dor_kst_id,kdt_kst_id) AS kst_id,
COALESCE(dor_mat_id,kdt_mat_id) AS mat_id
FROM (
SELECT
dor.dor_kst_id,
dor.dor_mat_id,
sum(dor.kogus * koefitsent::numeric) AS kogus,
sum(dor.kokku) AS kokku
FROM dokumentide_read dor
JOIN dokumendid dok
ON dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT NULL
GROUP BY
dor.dor_kst_id,
dor.dor_mat_id
) dor
FULL JOIN koostude_detailid kdt
ON dor.dor_mat_id = kdt.kdt_mat_id AND dor.dor_kst_id = kdt.kdt_kst_id;
The idea behind the view is to show supposed expenses (in table koostude_detailid)
compared to actual expenses (in tables dokumendid and dokumentide_read). Both refer to
materials (foreign keys kdt_mat_id and dor_mat_id) and belong to an assembly unit
(foreign keys kdt_kst_id and dor_kst_id). The report will show supposed and actual
expenses side by side for one assemby unit. So the view is queried like this:
explain select count(1) from v_tegelikud_kulud where kdt_kst_id = 1125 or dor_kst_id =
1125;
QUERY PLAN
Aggregate (cost=42.31..42.31 rows=1 width=16)
-> Merge Join (cost=41.42..42.03 rows=113 width=16)
Merge Cond: (("outer".dor_kst_id = "inner".kdt_kst_id) AND
("outer".dor_mat_id = "inner".kdt_mat_id))
Filter: (("inner".kdt_kst_id = 1125) OR ("outer".dor_kst_id = 1125))
-> Sort (cost=34.44..34.46 rows=8 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Subquery Scan dor (cost=33.25..34.31 rows=8 width=41)
-> Aggregate (cost=33.25..34.31 rows=8 width=41)
-> Group (cost=33.25..33.89 rows=84 width=41)
-> Sort (cost=33.25..33.47 rows=84 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Hash Join (cost=8.19..30.56 rows=84
width=41)
Hash Cond: ("outer".dor_dok_id =
"inner".dok_id)
-> Seq Scan on dokumentide_read dor
(cost=0.00..15.61 rows=761 width=37)
-> Hash (cost=8.10..8.10 rows=36
width=4)
-> Seq Scan on dokumendid dok
(cost=0.00..8.10 rows=36 width=4)
Filter: ((tyyp = 30) AND
(kinnitaja IS NOT NULL))
-> Sort (cost=6.98..7.27 rows=113 width=8)
Sort Key: kdt.kdt_kst_id, kdt.kdt_mat_id
-> Seq Scan on koostude_detailid kdt (cost=0.00..3.13 rows=113
width=8)
(20 rows)
When I disable seqscan (I don't have many rows in our development database), I get
following result:
explain select count(1) from v_tegelikud_kulud where kdt_kst_id = 1125 or dor_kst_id =
1125;
QUERY PLAN
-
Aggregate (cost=51.13..51.13 rows=1 width=16)
-> Merge Join (cost=40.39..50.85 rows=113 width=16)
Merge Cond: (("outer".kdt_kst_id = "inner".dor_kst_id) AND
("outer".kdt_mat_id = "inner".dor_mat_id))
Filter: (("outer".kdt_kst_id = 1125) OR ("inner".dor_kst_id = 1125))
-> Index Scan using kdt_uk on koostude_detailid kdt (cost=0.00..10.13
rows=113 width=8)
-> Sort (cost=40.39..40.41 rows=8 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Subquery Scan dor (cost=39.20..40.26 rows=8 width=41)
-> Aggregate (cost=39.20..40.26 rows=8 width=41)
-> Group (cost=39.20..39.83 rows=84 width=41)
-> Sort (cost=39.20..39.41 rows=84 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Merge Join (cost=0.00..36.51 rows=84
width=41)
Merge Cond: ("outer".dor_dok_id =
"inner".dok_id)
-> Index Scan using dor_dok_fk_i on
dokumentide_read dor (cost=0.00..20.91 rows=761 width=37)
-> Index Scan using dok_pk on dokumendid
dok (cos
Re: [SQL] full join in view
Tambet Matiisen wrote: You are right. After disabling seq_scan, it uses indexes just as you described. Unfortunately my view happens to use subquery: Don't disable seq_scan - sometimes it is better than indexscan. I had the same problem as you - find subject "sub-select with aggregate" on pgsql-sql mailing list dated on 2002-10-23. In my case exposing fields from subquery solved my problem. There is one more problem in your query - coalesce, which possibly disables any indexing in your view. Try to rewrite your view - subquery shouldn't return dor_kst_id and dor_mat_id null. Is dor_kst_id the same as kdt_kst_id and as mat_id? After some database practicing I found, that using the same name in all tables is much more comfortably For each material (materjalid) and koostud (koostud) you want to find some current value (koostude_detaild) and compare it to some sum (documentid...)? I'm not sure if I understand well your view, but here is my version of this view - without subquery: CREATE OR REPLACE VIEW v_tegelikud_kulud AS SELECT koostud.kst_id, materjalid.mat_id, sum(dor.kogus * koefitsent::numeric) AS kogus, sum(dor.kokku) AS kokku, FROM koostud k , materjalid m left join koostude_detailid kdt ON (m.mat_id = kdt.kdt_mat_id AND k.kst_id = kdt.kdt_kst_id) left join dokumentide_read dor ON (m.mat_id = dor.dor_mat_id AND k.kst_id = dor.dor_kst_id) left JOIN dokumendid dok ON (dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT NULL) group by koostud.kst_id, materjalid.mat_id, ; One more hint - create two-fields-indexes on koostude_detailid and dokuemntide_read (kdt_mat_id,kdt_kst_id) Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Data between different databases
I would like to know in how can i reference a table in a database A from a database B. In resume, i want to separate the data in my database in two others databases and make references for them. Thanks, Pedro Igor ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003
Re: [SQL] full join in view
First I would like to say, that I'm quite amazed. You even guessed table names right!
:) I did not expect such an in-depth analysis in such a short time. Thanks, Tomasz!
> -Original Message-
> From: Tomasz Myrta [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 14, 2003 11:51 AM
> To: Tambet Matiisen
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] full join in view
>
>
> Tambet Matiisen wrote:
>
> > You are right. After disabling seq_scan, it uses indexes
> just as you
> > described. Unfortunately my view happens to use subquery:
>
> Don't disable seq_scan - sometimes it is better than indexscan.
I just did it for testing and for one session. I never disable it on production server.
...
>
> There is one more problem in your query - coalesce, which possibly
> disables any indexing in your view. Try to rewrite your view
> - subquery
> shouldn't return dor_kst_id and dor_mat_id null.
>
Coalesce did not pose any problems. Unless I tried to filter using one of the
coalesce-fields, which does not use indeces of course.
> Is dor_kst_id the same as kdt_kst_id and as mat_id? After
> some database
> practicing I found, that using the same name in all tables is
> much more
> comfortably
>
This way I can refer most columns without prefixing them with table alias. But it's
anyway good habit to use table aliases, so this is not that important. I think in next
project I try it in your way.
> For each material (materjalid) and koostud (koostud) you want to find
> some current value (koostude_detaild) and compare it to some sum
> (documentid...)?
> I'm not sure if I understand well your view, but here is my
> version of
> this view - without subquery:
>
I tried to save few scans by not including "koostud" and "materjalid" in my original
query. Based on yours, I created a new version:
CREATE OR REPLACE VIEW v_tegelikud_kulud2 AS
SELECT
kst.kst_id,
mat.mat_id,
max(kdt.detaili_nr) AS detaili_nr,
max(kdt.arv) AS arv,
max(kdt.kulu) AS kulu,
max(kdt.yhik) AS yhik,
max(kdt.koefitsent) AS koefitsent,
max(kdt.eeldatav_hind) AS eeldatav_hind,
sum(dor.kogus * dor.koefitsent::numeric) AS kogus,
sum(dor.kokku) AS kokku
FROM koostud kst
CROSS JOIN materjalid mat
LEFT JOIN koostude_detailid kdt
ON kst.kst_id = kdt.kdt_kst_id AND mat.mat_id = kdt.kdt_mat_id
LEFT JOIN dokumentide_read dor
ON kst.kst_id = dor.dor_kst_id AND mat.mat_id = dor.dor_mat_id AND EXISTS
(
SELECT 1
FROM dokumendid dok
WHERE dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT
NULL
)
WHERE kdt.kdt_id IS NOT NULL AND dor.dor_id IS NOT NULL
GROUP BY kst.kst_id, mat.mat_id;
I do a cross join between "koostud" and "materjalid", because "FROM koostud kst,
materjalid mat" gave me syntax errors. Also I had to move "dokumendid" table to EXISTS
subquery, to get equivalent results with the original query. Just LEFT JOIN-ing it is
not enough and subquery troubled optimizer. There is also a WHERE condition to show
only those materials, that appear in one of the tables "koostude_detailid" or
"dokumentide_read". Here is the execution plan:
explain select count(1) from v_tegelikud_kulud2 where kst_id = 1125;
QUERY PLAN
---
Aggregate (cost=92.60..92.60 rows=1 width=107)
-> Subquery Scan v_tegelikud_kulud2 (cost=69.58..92.58 rows=8 width=107)
-> Aggregate (cost=69.58..92.58 rows=8 width=107)
-> Group (cost=69.58..91.00 rows=79 width=107)
-> Merge Join (cost=69.58..90.61 rows=79 width=107)
Merge Cond: (("outer".kst_id = "inner".dor_kst_id) AND
("outer".mat_id = "inner".dor_mat_id))
Join Filter: (subplan)
Filter: ("inner".dor_id IS NOT NULL)
-> Merge Join (cost=17.54..18.52 rows=79 width=66)
Merge Cond: (("outer".kst_id = "inner".kdt_kst_id)
AND ("outer".mat_id = "inner".kdt_mat_id))
Filter: ("inner".kdt_id IS NOT NULL)
-> Sort (cost=10.56..10.76 rows=79 width=8)
Sort Key: kst.kst_id, mat.mat_id
-> Nested Loop (cost=0.00..8.07 rows=79
width=8)
-> Index Scan using kst_pk on koostud
kst (cost=0.00..4.49 rows=1 width=4)
Index Cond: (kst_id = 1125)
-> Seq Scan on materjalid mat
(cost=0.00..2.79 rows=79 width=4)
-> Sort (cost=6.98..7.27 rows=113 width=58)
Sort Key: kdt.kdt_kst_id, kdt.kdt_mat_id
[SQL] database broken ?
hi all the pg_dump utilitie return the follow message. Database version: PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Archiver (db) version:7.1.2 Proceeding deespite mistmatch. getTables():SELECT (for PRIMARY KEY) failed on table AnomaliasEstacion. Exlanation from backend: ERROR: Attribute 'oid' not found Any ideas ? What's going wrong? The power failure on the server several times ago ,is posible couse this problem ? thanks in advance. Marcelo Diaz Cortez --- ¿Todavía no navegás con Keko? Hacé click aquí: http://www.keko.com.ar ---(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] database broken ?
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > Database version: PostgreSQL 7.2.3 on i686-pc-linux-gnu, > compiled by GCC 2.96 > Archiver (db) version:7.1.2 > Proceeding deespite mistmatch. > What's going wrong? It told you: you're using the wrong pg_dump. Apparently you have overridden that error check rather than finding where you put the right pg_dump. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] full join in view
On Tue, Jan 14, 2003 at 04:27:22PM +0200, Tambet Matiisen wrote: > > First I would like to say, that I'm quite amazed. You even guessed table names >right! :) I did not expect such an in-depth analysis in such a short time. Thanks, >Tomasz! It wasn't difficult - these names where in foreign keys definition. > > > > > > > Tambet Matiisen wrote: > > > > > > Is dor_kst_id the same as kdt_kst_id and as mat_id? After > > some database > > practicing I found, that using the same name in all tables is > > much more > > comfortably > > > This way I can refer most columns without prefixing them with table alias. But it's >anyway good habit to use table aliases, so this is not that important. I think in >next project I try it in your way. If you have joins like this: table1 join table2 using (field1) duplicates of field1 disappears and you don't need table name. > > > For each material (materjalid) and koostud (koostud) you want to find > > some current value (koostude_detaild) and compare it to some sum > > (documentid...)? > > I'm not sure if I understand well your view, but here is my > > version of > > this view - without subquery: > > > > I tried to save few scans by not including "koostud" and "materjalid" in my original >query. Based on yours, I created a new version: If you need only not null-kdt_id and dor_id, then just change your joins into inner joins. If you are sure, that you will get only not-null results, you don't need to include koostud and marerjalid. The result is: CREATE OR REPLACE VIEW v_tegelikud_kulud2 AS SELECT kdt.kdt_kst_id as kst_id, kdt.kdt_mat_id as mat_id, max(kdt.detaili_nr) AS detaili_nr, max(kdt.arv) AS arv, max(kdt.kulu) AS kulu, max(kdt.yhik) AS yhik, max(kdt.koefitsent) AS koefitsent, max(kdt.eeldatav_hind) AS eeldatav_hind, sum(dor.kogus * dor.koefitsent::numeric) AS kogus, sum(dor.kokku) AS kokku FROM koostude_detailid kdt JOIN dokumentide_read dor ON kdt.kdt_kst_id = dor.dor_kst_id AND kdt.kdt_mat_id = dor.dor_mat_id AND EXISTS ( SELECT 1 FROM dokumendid dok WHERE dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT NULL ) GROUP BY kst.kst_id, mat.mat_id; > But there are still few things that worry me: > 1. Cross join between koostud and materjalid. Table "materjalid" may have up to >1 rows and only 20-30 of them are actually needed. You don't need it anymore. Anyway I thought, that you have in your query "mat_id=.. and kst_id=.." > 2. Indeces on "koostude_detailid" and "dokumentide_read" are not used. Probably my >tables do not contain enough rows. Maybe I should generate more test data first. > 3. The cost of this query is twice as big, as my original query. It seems to me, >that SubPlan is causing this. I tried to move it to subquery, but then the optimizer >chose a totally different execution plan and seemingly was not able to use indeces of >"dokumentide_read" table. The version with subquery: > Now it should work better. Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
