pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
approve VKPts5 unsubscribe pgsql
2+ messages / 2 participants
[nested] [flat]

* approve VKPts5 unsubscribe pgsql
@ 2000-04-03 05:48 tszczachor@zke.com.pl
  2000-04-03 11:48 ` Re: approve VKPts5 unsubscribe pgsql Alexey V. Meledin <avm@webclub.ru>
  0 siblings, 1 reply; 2+ messages in thread

From: tszczachor@zke.com.pl @ 2000-04-03 05:48 UTC (permalink / raw)
  To: pgsql-sql

This is a multi-part message in MIME format.

------=_NextPart_000_0069_01BF9D3C.7C5AFE80
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable



------=_NextPart_000_0069_01BF9D3C.7C5AFE80
Content-Type: text/html;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-2" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0069_01BF9D3C.7C5AFE80--




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: approve VKPts5 unsubscribe pgsql
  2000-04-03 05:48 approve VKPts5 unsubscribe pgsql tszczachor@zke.com.pl
@ 2000-04-03 11:48 ` Alexey V. Meledin <avm@webclub.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Alexey V. Meledin @ 2000-04-03 11:48 UTC (permalink / raw)
  To: 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@webclub.ru><nick_as: <cureman>>>>





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-04-03 11:48 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-03 05:48 approve VKPts5 unsubscribe pgsql tszczachor@zke.com.pl
2000-04-03 11:48 ` Alexey V. Meledin <avm@webclub.ru>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox