Scott,
thank you.
> work_mem = 512
This is very easy to try. You can change work_mem for just a single
session, and this can in some cases help performance quite a bit, and in
others not at all.
I would not recommend having it lower than at least 4MB on a server like
that unless you have a lo
My test computer has PostgreSql 8.3, 4 GB RAM, SSD disks, Intel X2Extreme
CPU
So it is much faster than this prod server.
No idea how to emulate this environment.
I can create new db in prod server as old copy but this can be used in late
night only.
Well, a faster but comparable system may not
Scott,
thank you.
> work_mem = 512
This is very easy to try. You can change work_mem for just a single
session, and this can in some cases help performance quite a bit, and in
others not at all.
I would not recommend having it lower than at least 4MB on a server like
that unless you have a lo
I guess you have backups - take them, restore the database on a different
machine (preferably with the same / similar hw config) and tune the
queries on it.
After restoring all the tables / indexes will be 'clean' (not bloated), so
you'll see if performing VACUUM FULL / CLUSTER is the right solut
Adding limit clause causes very slow query:
explain analyze select * from firma2.dok where doktyyp='J' order by dokumnr
limit 100
"Limit (cost=0.00..4371.71 rows=100 width=1107) (actual
time=33189.971..33189.971 rows=0 loops=1)"
" -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..278740.
> Appoaches which probably does not change perfomance:
> 6. Upgrade to 8.4 or to 8.3.5
Both of these will improve performance a little, even with the same query plan
and same data. I would expect about a 10% improvement for 8.3.x on most memory
bound select queries. 8.4 won't be out for a few
Risky to try in prod server. Requires creating randomly distributed
product_id testcase to measure
difference.
What should I do next?
I guess you have backups - take them, restore the database on a
different machine (preferably with the same / similar hw config) and
tune the queries on it.
You could try writing a plpgsql function which would generate the data
set.
Or you could use your existing data set.
Creating 3.5 mln rows using stored proc is probably slow.
Probably it would be better and faster to use some random() and
generate_series() trick.
In this case others can try it a
An index-scan makes only sense if rid contains considerable more than
300 rows.
I'm sorry, I meant using index to get the row.
Andrus.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/p
am Sun, dem 23.11.2008, um 6:20:08 +0200 mailte Andrus folgendes:
> Gregory,
>
> > I would suggest running ANALYZE
> >idtellUued at some point before the problematic query.
>
> Thank you.
> After adding analyze all is OK.
> Is analyze command required in 8.3 also ?
Yes.
Andreas
--
Andreas
Andrus <[EMAIL PROTECTED]> schrieb:
> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
> Instead of using single key index, 8.1.4 scans over whole rid table.
> Sometimes idtelluued can contain more than single row so replacing join
> with equality is not possible.
>
> How
Andrus <[EMAIL PROTECTED]> schrieb:
> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
> Instead of using single key index, 8.1.4 scans over whole rid table.
> Sometimes idtelluued can contain more than single row so replacing join
> with equality is not possible.
>
> How
12 matches
Mail list logo
