On Apr 21, 9:52 pm, kevin.gritt...@wicourts.gov ("Kevin Grittner")
wrote:
> I wrote:
> > ALTER TABLE ALTER plugins_guide_address
> > ALTER COLUMN city_id SET STATISTICS 1000;
>
> One too many ALTERs in there. Should be:
>
> ALTER TABLE plugins_guide_address
> ALTER COLUMN city_id SET STATISTIC
> 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 F
I wrote:
> ALTER TABLE ALTER plugins_guide_address
> ALTER COLUMN city_id SET STATISTICS 1000;
One too many ALTERs in there. Should be:
ALTER TABLE plugins_guide_address
ALTER COLUMN city_id SET STATISTICS 1000;
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@pos
norn wrote:
>> (1) Try it without the ORDER BY clause and the LIMIT.
> W/o the 'order by' it works instantly (about 1ms!)
> W/o the limit it takes 1.4 seconds
>>(2) Temporarily take that top index out of consideration
> It works nice! Query takes about 0.6 seconds as expected!
> So, as we
Kevin,
thanks for your time!
Here the requested tests.
> (1) Try it without the ORDER BY clause and the LIMIT.
W/o the 'order by' it works instantly (about 1ms!)
Limit (cost=0.00..3.59 rows=5 width=4) (actual time=0.127..0.229
rows=5 loops=1)
-> Nested Loop (cost=0.00..277863.53 rows=386544
"Kevin Grittner" wrote:
> (3) Try it like this (untested, so you may need to fix it up):
>
> explain analyze
> SELECT core_object.id
> from (SELECT id, city_id FROM "plugins_guide_address")
>"plugins_guide_address"
> JOIN "plugins_plugin_addr"
> ON ("plugins_plugin_addr"."addre
I'm also wondering if a re-clustering of the table would work based on
the index that's used.
such that:
CLUSTER core_object USING plugins_plugin_addr_oid_id;
and see if that makes any change in the differences that your seeing.
On 04/13/2010 02:24 PM, Kevin Grittner wrote:
> norn wrote:
>
>
norn wrote:
> I am wondering why there are so big gap between two limits and how
> to avoid this...
I think we've already established that it is because of the
percentage of the table which must be scanned to get to the desired
number of rows. The problem is exacerbated by the fact that it's
On Apr 13, 5:28 am, kevin.gritt...@wicourts.gov ("Kevin Grittner")
wrote:
> The cost settings help the optimizer make good decisions about plan
> choice. I guess I don't have much reason to believe, at this point,
> that there is a better plan for it to choose for this query. Do you
> think you s
norn wrote:
> I restarted Postgresql with new settings and got no performance
> improvements in this particular query...
The cost settings help the optimizer make good decisions about plan
choice. I guess I don't have much reason to believe, at this point,
that there is a better plan for it t
Kevin,
I appreciate your help very much!
> Since the LIMIT 3 and LIMIT 4 queries generated exactly the same
> plan, the increased time for LIMIT 4 suggests that there are 3
> matching rows which are near the end of the index it is scanning, but
> the fourth one is much farther in.
Yes, you are rig
On Apr 10, 6:48 am, robertmh...@gmail.com (Robert Haas) wrote:
> On Tue, Apr 6, 2010 at 8:42 PM, norn wrote:
> > I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
> > getting greater than some value (greater than 3 in my case), query
> > takes 4-5 secs instead of 0.25ms. All of
[rearranging to put related information together]
norn
Since the LIMIT 3 and LIMIT 4 queries generated exactly the same
plan, the increased time for LIMIT 4 suggests that there are 3
matching rows which are near the end of the index it is scanning, but
the fourth one is much farther in.
Sinc
1 ) Limit (cost=0.00..9.57 rows=3 width=4) (actual time=*0.090..0.138*
rows=3 loops=1)
2 ) Limit (cost=0.00..12.76 rows=4 width=4) (actual
time=*0.091..4436.795* rows=4 loops=1)
1 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4)
(actual time=*0.088..0.136* rows=*3* loops=1)
2
On Tue, Apr 6, 2010 at 8:42 PM, norn wrote:
> I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
> getting greater than some value (greater than 3 in my case), query
> takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in
> place. I have no idea what to do, so an
Kevin, thanks for your attention!
I've read SlowQueryQuestions, but anyway can't find bottleneck...
Here requested information:
OS: Ubuntu 9.10 64bit, Postgresql 8.4.2 with Postgis
Hardware: AMD Phenom(tm) II X4 945, 8GB RAM, 2 SATA 750GB (pg db
installed in software RAID 0)
Please also note that
norn wrote:
> I have some mysterious slow downs with ORDER BY and LIMIT. When
> LIMIT getting greater than some value (greater than 3 in my case),
> query takes 4-5 secs instead of 0.25ms. All of the necessary
> indexes are in place. I have no idea what to do, so any advices
> are welcome!
Cou
17 matches
Mail list logo
