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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-04-21 13:52:55 | Re: significant slow down with various LIMIT |
Previous Message | Greg Smith | 2010-04-21 06:45:55 | Re: Very high effective_cache_size == worse performance? |