Re: query plan not optimal

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.56..4001768.10 rows=479020 width=26) (actual time=2.303..15371.237 rows=479020 loops=1)
   Output: path.pathid, batch.filename
   Buffers: shared hit=2403958 read=7539
   ->  Seq Scan on public.batch  (cost=0.00..11727.20 rows=479020 width=85) (actual time=0.340..160.142 rows=479020 loops=1)
         Output: batch.path, batch.filename
         Buffers: shared read=6937
   ->  Index Scan using idx_path on public.path  (cost=0.56..8.32 rows=1 width=16) (actual time=0.030..0.031 rows=1 loops=479020)
         Output: path.pathid, path.path
         Index Cond: (path.path = batch.path)
         Buffers: shared hit=2403958 read=602
 Total runtime: 15439.043 ms


As you can see, more than twice as fast, and a very high hit ratio on the path table, even if we start from a cold cache (I did, here, both PostgreSQL and OS). We have an excellent hit ratio because the batch table contains few different path (several files in a directory), and is already quite clustered, as it comes from a backup, which is of course performed directory by directory.

What is your effective_cache_size set to?

Cheers,

Jeff

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux