Re: query speed depends on lifetime of frozen db?.. Sorry

From: Andriy Tkachuk <ant(at)imt(dot)com(dot)ua>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Postgresql General <pgsql-general(at)postgresql(dot)org>
Subject: Re: query speed depends on lifetime of frozen db?.. Sorry
Date: 2002-09-28 13:58:21
Message-ID: 20020928164842.U68728-100000@pool.imt.com.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

does one have any idea why there is so incredible differences
between plans:

bb=# EXPLAIN ANALYZE select * from bills b, users u where b.uid = u.uid and u.uid=10;
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..1874.57 rows=356 width=403) (actual time=0.18..0.18 rows=0 loops=1)
-> Index Scan using users_uid_idx on users u (cost=0.00..5.79 rows=1 width=301) (actual time=0.17..0.17 rows=0 loops=1)
-> Index Scan using bill_uid on bills b (cost=0.00..1861.69 rows=567 width=102)
Total runtime: 0.67 msec

EXPLAIN
bb=# EXPLAIN ANALYZE select * from bills b, users u where b.uid = u.uid and u.parent=10;
NOTICE: QUERY PLAN:

Hash Join (cost=6.49..3010.47 rows=2016 width=403) (actual time=4831.21..4831.21 rows=0 loops=1)
-> Seq Scan on bills b (cost=0.00..2462.85 rows=103185 width=102) (actual time=0.11..3687.68 rows=103185 loops=1)
-> Hash (cost=6.48..6.48 rows=6 width=301) (actual time=0.20..0.20 rows=0 loops=1)
-> Index Scan using users_parent_idx on users u (cost=0.00..6.48 rows=6 width=301) (actual time=0.19..0.19 rows=0 loops=1)
Total runtime: 4831.84 msec

EXPLAIN
bb=# SELECT count(*) from users where parent = 10;
count
-------
0
(1 row)

Thanks,
Andriy.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Peter Kovacs 2002-09-28 14:20:48 Re: [JDBC] Prepared statement performance...
Previous Message Martijn van Oosterhout 2002-09-28 12:13:02 Re: query speed depends on lifetime of frozen db?