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

Re: significant slow down with various LIMIT

From: norn <andrey(dot)perliev(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: significant slow down with various LIMIT
Date: 2010-04-21 08:24:08
Message-ID: 20427682-16f8-4340-afd7-1222977c440d@12g2000yqi.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-performance
> Try this:
>
> ALTER TABLE ALTER plugins_guide_address
>   ALTER COLUMN city_id SET STATISTICS 1000;
> ANALYZE plugins_guide_address;
>
> Then try your query.
No luck... The same query time...

> I have one more diagnostic query to test, if the above doesn't work:
>
> explain analyze
> SELECT id FROM
>   (
>     SELECT core_object.id
>       FROM "core_object"
>       JOIN "plugins_plugin_addr"
>         ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
>       JOIN "plugins_guide_address"
>         ON ("plugins_plugin_addr"."address_id" =
>             "plugins_guide_address"."id")
>       WHERE "plugins_guide_address"."city_id" = 4535
>   ) x
>   ORDER BY id DESC
>   LIMIT 4;

Limit  (cost=0.00..8.29 rows=4 width=4) (actual time=0.284..1322.792
rows=4 loops=1)
   ->  Merge Join  (cost=0.00..993770.68 rows=479473 width=4) (actual
time=0.281..1322.787 rows=4 loops=1)
         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
         ->  Nested Loop  (cost=0.00..887841.46 rows=479473 width=4)
(actual time=0.194..1201.318 rows=4 loops=1)
               ->  Index Scan Backward using
plugins_plugin_addr_oid_id on plugins_plugin_addr
(cost=0.00..51546.26 rows=1980627 width=8) (actual time=0.117..87.035
rows=359525 loops=1)
               ->  Index Scan using plugins_guide_address_pkey on
plugins_guide_address  (cost=0.00..0.41 rows=1 width=4) (actual
time=0.003..0.003 rows=0 loops=359525)
                     Index Cond: (plugins_guide_address.id =
plugins_plugin_addr.address_id)
                     Filter: (plugins_guide_address.city_id = 4535)
         ->  Index Scan Backward using core_object_pkey on
core_object  (cost=0.00..91309.16 rows=3450658 width=4) (actual
time=0.079..73.071 rows=359525 loops=1)
 Total runtime: 1323.065 ms
(10 rows)


In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-04-21 13:52:55
Subject: Re: significant slow down with various LIMIT
Previous:From: Greg SmithDate: 2010-04-21 06:45:55
Subject: Re: Very high effective_cache_size == worse performance?

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