This query is a couple orders of magnitude slower the first result is
9.2.1, the second 9.1

=# explain analyze SELECT note_sets."id" AS t0_r0,
note_sets."note_set_source_id" AS t0_r1, note_sets."parent_id" AS t0_r2,
note_sets."business_entity_id" AS t0_r3, note_sets."created_at" AS t0_r4,
note_sets."updated_at" AS t0_r5, note_sets."created_by" AS t0_r6,
note_sets."updated_by" AS t0_r7, note_set_sources."id" AS t1_r0,
note_set_sources."name" AS t1_r1, note_set_sources."code" AS t1_r2,
note_set_sources."description" AS t1_r3, note_set_sources."status" AS
t1_r4, note_set_sources."created_at" AS

Dear All

I am currently implementing using a compressed binary storage scheme
genotyping data. These are basically vectors of binary data which may be
megabytes in size.

Our current implementation uses the data type bit varying.

What we want to do is very simple: we want to retrieve such records from
the database and transfer it unaltered to the client which will do
something (uncompressing) with it.

[Please CC me on replies, as I'm not subscribed; thank you.]

I've ran into a problem with the query planner and IN (subquery)
conditions which I suspect to be a bug. I'll attempt to describe the
relevant details of my database and explain which behaviour I find
unexpected. I've also tried to trigger this behaviour in a clean
database; I think I've succeeded, but the conditions are a bit
different, so perhaps it's a different problem.

We have a web application where we create a schema or a database with a
number of tables in it for each customer. Now we have about 2600 clients.

The problem we met using a separate DB for each client is that the creation
of new DB can take up to 2 minutes, that is absolutely unacceptable. Using
schemes instead (one DB with a number of schemes containing similar tables
in it) solved this problem (schemes are created in a couple of seconds), but
created two other blocking points:
1. sometimes creation of a new table in a schema takes up to 5 seconds.

I'm bringing up a new type of server using Intel E5-2620 (unisocket)
which was selected for good SpecIntRate performance vs cost/power (201
for $410 and 95W).

Was assuming it was 6-core but I just noticed it has HT which is
currently enabled since I see 12 cores in /proc/cpuinfo

Question for the performance experts : is it better to have HT enabled
or disabled for this generation of Xeon ?
Workload will be moderately concurrent, small OLTP type transactions.
We'll also run a few low-load VMs (using KVM) and a big Java application.

Any thoughts welcome.
Thanks.

where should i get the original working source code of DBT-1 benchmark and it is compatible with postgres 9.2?

Best Regards

Hello everyone,

If one would create a postgres performance tool, what would one like to measure aside from the obvious things common to all databases (query times, locks etc)?

I'm in the slightly difficult situation of being asked to port "Jet Profiler for MySQL" (<a href="http://www.jetprofiler.com" title="http://www.jetprofiler.com">http://www.jetprofiler.com</a>) for use with postgres (a "Jet Profiler for PostgreSQL" if you will).

I'm running a server with lots of counts and calculations.
currently its ubuntu server is freebsd faster?

also this is a i386 machine.

or linux and bsd is about the same.

this is not to be an argument just looking. Current benchmarks to compare

thanks

Hi there,

I work for VMware with our Postgres performance team. We recently came across a dbt2 performance regression from 9.1.6 to 9.2.1. We have done some profiling and don't see anything obvious.

Hi all :)

I'm here again.
This time I'll provide more details (explain analyze, data-type, and
indexes), hope it will be enough :)

The query that is performing a plan that i do not understand is the
following:
This is the explain analyze:

Shaun Thomas wrote:

Hi all,

I was wondering if it is safe to install pg_buffercache on production
systems?

Thank you.

Hi all

I have a problem with a data import procedure that involve the following query:

select a,b,c,d
from big_table b
join data_sequences_table ds
on b.key1 = ds.key1 and b.key2 = ds.key2
where ds.import_id=xxxxxxxxxx

The "big table" has something like 10.000.000 records ore more
(depending on the table, there are more than one of them).
The data are uploaded in 20k record blocks, and the keys are written
on "data_sequences_table".

Hi,
When i start my postgres. Iam getting this error. I had
installed 8.4 and 9.1
It was working good yesterday but not now.
service postgresql restart
* Restarting PostgreSQL 8.4databaseserver
* The PostgreSQL server failed to start. Please check the log output.

If i see the log. it shows yesterday's log report. please give me suggestion
Thanks for reply.

Hello all,

I have been pulling my hair out over the last few days trying to get any useful performance out of the following
painfully slow query.
The query is JPA created, I've just cleaned the aliases to make it more readable.
Using 'distinct' or 'group by' deliver about the same results, but 'distinct' is marginally better.
Hardware is pretty low end (a test box), but is mostly dedicated to PostgreSQL.
The box spec and configuration is included at the end of this post - Some of the values have been changed just to see if
things get better.
Inserts have also become extremely slow.

Higher OFFSET settings may be slow because it has to read through
OFFSET result rows before returning anything.

Hi, thanks for any help. I've tried to be thorough, but let me know if I should
provide more information.

A description of what you are trying to achieve and what results you expect:
I have a large (3 million row) table called "tape" that represents files,
which I join to a small (100 row) table called "filesystem" that represents
filesystems.

All... first let me say thank you for this forum.... I am new to it and
relatively new to postgres, more of a sysadmin than a DBA, but let me
explain my issue. I'll try also to post relevant information as well.

Our IT group took over an app that we have running using postgres and it has
been on version 8.2.11 since we acquired it. It is time to get current, so
I have created instances of our production database that mirror exact
hardware for our existing implementation on version 8.2.11 (running Fedora
Core 8 - wow I know) and also version 9.1.6 on Fedora 17.

You can set those per tablespace.

The goal should be to set the cost factors so that they model actual
costs for you workload in your environment. In what cases have you
seen the sequential scan of a large number of adjacent pages from
disk take longer than randomly reading the same number of pages from
disk? (I would love to see the bonnie++ number for that, if you have
them.)

-Kevin

The idea is to model actual costs on your system.

One thing to look for is a connection stuck in "idle in transaction"
or old prepared transactions in pg_prepared_xacts. Either will cause
all sorts of problems, but if you are using serializable transactions
the error you are seeing is often the first symptom. I'm starting to
think we should add something about that to the hint.

On the other hand, it could just be that you need to increase the
setting the hint currently references. For complex databases it is
definitely on the low side.

Hi,

i've got a very strange problem on PostgreSQL 8.4, where the queryplaner goes absolutely havoc, when slightly changing one parameter.

First the Tables which are involved:
1.

I have a problem with prepared statements choosing a bad query plan - I was hoping that 9.2 would have eradicated the problem :(

Taken from the postgresql log:

<2012-10-23 15:21:03 UTC acme_metastore 13798 5086b49e.35e6> LOG: duration: 20513.809 ms execute S_6: SELECT S.Subj, S.Prop, S.Obj
FROM jena_g1t1_stmt S WHERE S.Obj = $1 AND S.Subj = $2 AND S.Prop = $3 AND S.GraphID = $4

<2012-10-23 15:21:03 UTC acme_metastore 13798 5086b49e.35e6> DETAIL: parameters: $1 =

Am I reading this correctly -- it appears that if SSL negotiation is
enabled for a connection (say, when using pg_basebackup over a WAN) that
compression /*is automatically used*/ (provided it is supported on both
ends)?

Is there a way to check and see if it _*is*_ on for a given connection?

I was looking to hack in zlib support and saw that appears to be
already-present support, provided SSL connection security is enabled.

Hi,

I have a self-referencing table that defines a hierarchy of projects and sub-projects.

This is the table definition:

CREATE TABLE project
(
project_id integer primary key,
project_name text,
pl_name text,
parent_id integer
);

ALTER TABLE project
ADD CONSTRAINT project_parent_id_fkey FOREIGN KEY (parent_id)
REFERENCES project (project_id)
ON UPDATE NO ACTION
ON DELETE NO ACTION;

The table contains ~11000 rows

The following statement:

with recursive project_tree as (
select project_id,
parent_id,
pl_name as root

Hi all,

I've created a test table containing 21 million random dates and
times, but I get wildly different results when I introduce a
functional index then ANALYSE again, even though it doesn't use the
index:

postgres=# CREATE TABLE test (id serial, sampledate timestamp);
CREATE TABLE
postgres=# INSERT INTO test (sampledate) SELECT '1970-01-01
00:00:00'::timestamp + (random()*1350561945 || ' seconds')::interval
FROM generate_series(1,21000000);
INSERT 0 21000000
postgres=# VACUUM;
VACUUM
postgres=# EXPLAIN SELECT extract(month FROM sampledate), count(*)
FROM test GROUP BY extract(month FROM

We've run into a perplexing issue with a customer database.

Hi guys,

PG = 9.1.5
OS = winDOS 2008R8

I have a table that currently has 207 million rows.
there is a timestamp field that contains data.
more data gets copied from another database into this database.
How do I make this do an index scan instead?
I did an "analyze audittrailclinical" to no avail.
I tested different indexes - no same behavior.

The query does this:

SELECT
audittrailclinical.pgid,
audittrailclinical.timestamp,
mmuser.logon,
audittrailclinical.entityname,
audittrailclinical.clinicalactivity,
audittrailclinical.audittraileventcode,
account.accountnumber,