Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group