Re: query speed depends on lifetime of frozen db?.. Sorry - Mailing list 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=0loops=1) 
Total runtime: 4831.84 msec

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


Thanks,
  Andriy.