Hi all,
i have strange problem with performance in PostgreSQL (8.1.9). My problem
shortly:
I'm using postgreSQL via JDBC driver (postgresql-8.1-404.jdbc3.jar) and
asking the database for search on table with approximately 3 000 000
records.
I have created functional index table(lower(href)
On Fri, 22 Feb 2008, Kynn Jones wrote:
Hi. I'm trying to optimize...
(Q1) SELECT a1.word, a2.word
FROM T a1 JOIN T a2 USING ( zipk )
WHERE a1.type = int1
AND a2.type = int2;
Okay, try this:
Create an index on T(type, zipk), and then CLUSTER on that index. That
So, this email is directed much more towards Postgres Powers That Be. I
came across this problem a while ago, and I haven't checked whether it has
been improved.
On Mon, 25 Feb 2008, I wrote:
Hi. I'm trying to optimize...
(Q1) SELECT a1.word, a2.word
FROM T a1 JOIN T a2 USING (
The thing to remember here is that prepared statements are only planned once
and strait queries are planned for each query.
When you give the query planner some concrete input like in your example
then it will happily use the index because it can check if the input starts
with % or _. If you use
On Feb 24, 2008, at 4:27 PM, Scott Marlowe wrote:
On Sun, Feb 24, 2008 at 6:05 PM, Sean Leach [EMAIL PROTECTED] wrote:
On Feb 24, 2008, at 4:03 PM, Scott Marlowe wrote:
What version pgsql is this? If it's pre 8.0 it might be worth
looking
into migrating for performance and maintenance
On Sun, 24 Feb 2008, Tom Lane wrote:
Sean Leach [EMAIL PROTECTED] writes:
I have a table, that in production, currently has a little over 3
million records in production. In development, the same table has
about 10 million records (we have cleaned production a few weeks
ago).
You mean the
On Mon, Feb 25, 2008 at 8:45 AM, Matthew [EMAIL PROTECTED] wrote:
On Fri, 22 Feb 2008, Kynn Jones wrote:
Hi. I'm trying to optimize...
(Q1) SELECT a1.word, a2.word
FROM T a1 JOIN T a2 USING ( zipk )
WHERE a1.type = int1
AND a2.type = int2;
Okay, try this:
On Mon, 25 Feb 2008, Kynn Jones wrote:
This is just GREAT!!! It fits the problem to a tee.
It makes the queries quick then?
Matthew
--
The only secure computer is one that's unplugged, locked in a safe,
and buried 20 feet under the ground in a secret location...and i'm not
even too sure
Joel Stevenson [EMAIL PROTECTED] writes:
Also, it might be worth enabling log_lock_waits to see if the slow
notifies are due to having to wait on some lock or other.
Turning on log_lock_waits shows that there is a lot of waiting for
locks on the pg_listener table ala:
Interesting. The
Also, it might be worth enabling log_lock_waits to see if the slow
notifies are due to having to wait on some lock or other.
Turning on log_lock_waits shows that there is a lot of waiting for
locks on the pg_listener table ala:
process 22791 still waiting for ExclusiveLock on relation 2614
At 2:57 PM -0500 2/25/08, Tom Lane wrote:
It's weird that the behavior is robust for you but I can't make it
happen at all. Would you show the output of pg_config, as well as
all your nondefault postgresql.conf settings?
pg_config:
BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/doc
I have a cascading delete trigger that is obviously using a seqscan.
(Explain analyze shows that trigger as taking over 1000s while all
other triggers are 1s. The value in test delete didn't even appear in
this child table, so an index scan would have been almost instant.)
If I do
DELETE FROM
Sean Leach wrote:
On Feb 24, 2008, at 4:27 PM, Scott Marlowe wrote:
Urg. Then I wonder how your indexes are bloating but your table is
not... you got autovac running? No weird lock issues? It's a side
issue right now since the table is showing as non-bloated (unless
you've got a
On Feb 25, 2008, at 1:19 PM, Stephen Denne wrote:
So should I do a vacuum full and then hope this doesn't
happen again?
Or should I run a VACUUM FULL after each aggregation run?
If your usage pattern results in generating all of that unused space
in one transaction, and no further inserts
Sean Leach wrote
On Feb 25, 2008, at 1:19 PM, Stephen Denne wrote:
So should I do a vacuum full and then hope this doesn't
happen again?
Or should I run a VACUUM FULL after each aggregation run?
If your usage pattern results in generating all of that
unused space
in one
On Feb 25, 2008, at 2:59 PM, Stephen Denne wrote:
Please know that I'm very new at advising PostgreSQL users how they
should tune their system...
I'd never have known it if you hadn't said anything
My understanding of your vacuum verbose output was that it was
pointing out that
16 matches
Mail list logo
