From: | "Alexey V(dot) Meledin" <avm(at)webclub(dot)ru> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: approve VKPts5 unsubscribe pgsql |
Date: | 2000-04-03 11:48:05 |
Message-ID: | 3658.000403@webclub.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi!
1. I have:
vladimir=> explain SELECT acc.ifs_account_id FROM
vladimir-> ifs_account acc,
vladimir-> ifs_tree_default def,
vladimir-> ifs_account_tree_data atd
vladimir-> WHERE
vladimir-> acc.ifs_status_id = 12
vladimir-> AND atd.ifs_tree_id IN(14,26)
vladimir-> AND def.ifs_tree_default_id IN(587,175)
vladimir-> AND atd.ifs_account_id = acc.ifs_account_id
vladimir-> AND atd.ifs_data_id = def.ifs_data_id;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00 rows=1 width=16)
-> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4)
-> Seq Scan (cost=757.02 rows=1 width=12)
(**) -> ??? (cost=757.02 rows=1 width=12)
-> Hash Join (cost=757.02 rows=1 width=12)
-> Seq Scan on ifs_account_tree_data atd (cost=751.76 rows=3 width=8
)
-> Hash (cost=4.07 rows=3 width=4)
-> Index Scan using xpkifs_tree_default, xpkifs_tree_default on
ifs_tree_default def (cost=4.07 rows=3 width=4)
What mean "???" at (**).
There is no index? Or unknown method?
2. Seq Scan - Sequential Scan - this mean FULL TABLE SCAN (DATA SCAN)?
3. I try to play with indeces on this tables
3.1. Setting all btree indeces
explain SELECT acc.ifs_account_id FROM
ifs_account acc,
ifs_tree_default def,
ifs_account_tree_data atd
WHERE
acc.ifs_status_id = 12
AND atd.ifs_tree_id IN(14,26)
AND def.ifs_tree_default_id IN(587,175)
AND atd.ifs_account_id = acc.ifs_account_id
AND atd.ifs_data_id = def.ifs_data_id;
Nested Loop (cost=0.00 rows=1 width=16)
-> Nested Loop (cost=0.00 rows=1 width=12)
-> Seq Scan on ifs_tree_default def (cost=0.00 rows=0 width=4)
-> Seq Scan on ifs_account_tree_data atd (cost=0.00 rows=0 width=8)
-> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4)
Not optimal way I think
2. I've replace btree indexes on relation
AND atd.ifs_data_id = def.ifs_data_id;
with:
create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id);
create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id);
PS: Can't replace on on ifs_account_id because of PRIMARY KEY (may be
direct Alternative Entry helps me, but is it right way to optimize?)
so:
Nested Loop (cost=0.00 rows=1 width=16)
-> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4)
-> Seq Scan (cost=10.17 rows=1 width=12)
-> ??? (cost=10.17 rows=1 width=12)
-> Nested Loop (cost=10.17 rows=1 width=12)
-> Index Scan using xpkifs_tree_default, xpkifs_tree_default on ifs_tree_default def (cost=4.07 ows=3 width=4)
-> Index Scan using xif588ifs_account_tree_data on ifs_account_tree_d
ata atd (cost=2.03 rows=3 width=8)
3. I've delete all indexes on
AND atd.ifs_data_id = def.ifs_data_id;
Then I've got:
Nested Loop (cost=0.00 rows=1 width=16)
-> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4)
-> Seq Scan (cost=757.02 rows=1 width=12)
-> ??? (cost=757.02 rows=1 width=12)
-> Hash Join (cost=757.02 rows=1 width=12)
-> Seq Scan on ifs_account_tree_data atd (cost=751.76 rows=3 width=8
)
-> Hash (cost=4.07 rows=3 width=4)
-> Index Scan using xpkifs_tree_default, xpkifs_tree_default on
ifs_tree_default def (cost=4.07 rows=3 width=4)
Hash Join appears. But there is no indexes, so "Table Scan" appears
(approximately 18000 rows in ifs_account_tree_data)? BAD!?!
So, where is the optimal way?
To make separate queries and use temporary tables to decrease JOINs?
Regards, Alexey V. Meledin
InterForge Developers Group, Saint-Petersburg
look_to: <www.etcompany.ru><www.businessweb.ru>
<www.inplan.spb.ru><www.pia.ru>>>>>>>>>>>>>>>>>
mail_to: <avm(at)webclub(dot)ru><nick_as: <cureman>>>>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-04-03 14:32:06 | Re: planner complaints (was approve VKPts5 unsubscribe pgsql) |
Previous Message | Zot O'Connor | 2000-04-03 07:43:36 | OFFTOPIC: I will be in Montreal April 9-12, Atlanta Mat 14-19th |