Re: [GENERAL] Multithreaded query onto 4 postgresql instances

On 14 Feb 2011, at 9:38, Alessandro Candini wrote: I performed tests with a query returning more or less 10 records and using my C module I obtain the following results (every test performed cleaning cache before): - single db: 9.555 sec - splitted in 4: 5.496 sec Is that a single query

Re: [GENERAL] Multithreaded query onto 4 postgresql instances

Il 14/02/2011 21:00, Allan Kamau ha scritto: On Mon, Feb 14, 2011 at 10:38 AM, Alessandro Candini wrote: No, this database is on a single machine, but a very powerful one. Processors with 16 cores each and ssd disks. I already use partitioning and tablespaces for every instance of my db and I

Re: [GENERAL] Multithreaded query onto 4 postgresql instances

* Alessandro Candini wrote: Il 14/02/2011 21:00, Allan Kamau ha scritto: On Mon, Feb 14, 2011 at 10:38 AM, Alessandro Candini wrote: No, this database is on a single machine, but a very powerful one. Processors with 16 cores each and ssd disks. I already use partitioning and tablespaces for

Re: [GENERAL] Using Bitmap scan instead of Seq scan

On Mon, Feb 14, 2011 at 12:40 PM, Ahmed Ossama wrote: > I did a REINDEX and ANALYZE on the table, disabled the seqscan and ran the > query again with the same result. > > My question how do I make PostgreSQL always uses bitmap scan instead of seq > scan? > > Any advice is very much appreciated. >

Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL

2011/2/15 Merlin Moncure > On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin > wrote: > > 2011/2/14 Merlin Moncure > >> > >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson > > >> wrote: > >> > Dear PostgreSQL community, > >> > > >> > Please consider the following minimal example: > >> > > >> >

Re: [GENERAL] database design

Just curious; is it that you have never designed a multi-branch/company database before or do you have little or no database experience at all? As mentioned, you want to tag these records with the branch primary key and relevant date or dates. The question still remains whether you are dealing

Re: [GENERAL] read and restore deleted record

Start from vacuum feature information from PGSQL helps documentation, it telling me that Postgresql didn’t delete data permanently when we execute delete command, it just made the data invalid. By following this email archive : http://archives.postgresql.org/pgsql-admin/2005-01/msg00176.php I

Re: [GENERAL] Speeding up index scans by truncating timestamp?

> > Would creating an index on the timestamp truncated to the *day* make the > index more efficient for queries which are interested in events falling in a > range of 7+ days? > I gave this a shot, changing the index to be on date_trunc('day', timestamp). PostgreSQL (8.2) then decided not to use

Re: [GENERAL] Speeding up index scans by truncating timestamp?

On Feb 15, 2011, at 10:00, Derrick Rice wrote: >> >> Would creating an index on the timestamp truncated to the *day* make the >> index more efficient for queries which are interested in events falling in a >> range of 7+ days? >> > > I gave this a shot, changing the index to be on date_trunc('

Re: [GENERAL] Speeding up index scans by truncating timestamp?

On Tue, Feb 15, 2011 at 10:00 AM, Derrick Rice wrote: > Is the query optimizer capable of using the relationship between an index on > date_trunc(foo) and a query with "where foo < bar and foo > baz" ?  At this > point the question is to satisfy my own curiosity. No. The query has to use the same

Re: [GENERAL] finding bogus UTF-8

Glenn Maynard wrote: On Thu, Feb 10, 2011 at 2:02 PM, Scott Ribe > wrote: I know that I have at least one instance of a varchar that is not valid UTF-8, imported from a source with errors (AMA CPT files, actually) before PG's checking was as string

Re: [GENERAL] read and restore deleted record

You read the doc about archiving and pitr. 2011/2/15, Emi Lu : > >> Start from vacuum feature information from PGSQL helps documentation, it >> telling me that Postgresql didn’t delete data permanently when we >> execute delete command, it just made the data invalid. By following this >> email arc

[GENERAL] PostgreSQL ODBC Driver Help

I'm trying to figure out how I can have users in the office connect their Microsoft Office 2007 clients to our company database server running PostgreSQL 8.4.7. I've configured PostgreSQL to accept incoming connections and allow users to login however I read that I need to have each client install

Re: [GENERAL] PostgreSQL ODBC Driver Help

On Feb 15, 2011, at 12:25 PM, Carlos Mennens wrote: > I'm trying to figure out how I can have users in the office connect > their Microsoft Office 2007 clients to our company database server > running PostgreSQL 8.4.7. I've configured PostgreSQL to accept > incoming connections and allow users to

Re: [GENERAL] PostgreSQL ODBC Driver Help

On Tue, Feb 15, 2011 at 12:46 PM, A.M. wrote: > I googled "connect excel postgresql" and found this: > > http://port25.technet.com/videos/research/excelopendbprimer.pdf > > which seems to take one through all the steps. I have been using that actual .PDF as a guide and I guess it's different on O

Re: [GENERAL] Multithreaded query onto 4 postgresql instances

On 15 Feb 2011, at 9:32, Alessandro Candini wrote: >> Is that a single query on that one DB compared to 4 queries on 4 DB's? How >> does a single DB with 4 parallel queries perform? I'd expect that to win >> from 4 DB's, due to the overhead those extra DB instances are generating. > > Maybe my

[GENERAL] Best RDB book to suggest

Hi list, As there's many developpers of databases in this list, I thought maybe someone can suggest me one of the best relational database books and that explain in a deep manner about all what concern relational databases. Best regards, Kamler Alphab

Re: [GENERAL] Best RDB book to suggest

On 02/15/11 10:39 AM, Alpha Beta wrote: Hi list, As there's many developpers of databases in this list, I thought maybe someone can suggest me one of the best relational database books and that explain in a deep manner about all what concern relational databases. for the theory and fundamen

Re: [GENERAL] Best RDB book to suggest

On Tue, 15 Feb 2011, John R Pierce wrote: As there's many developpers of databases in this list, I thought maybe someone can suggest me one of the best relational database books and that explain in a deep manner about all what concern relational databases. for the theory and fundamentals behi

Re: [GENERAL] Speeding up index scans by truncating timestamp?

On Tue, Feb 15, 2011 at 10:16 AM, Michael Glaesemann wrote: > Providing the table definition, queries, and EXPLAIN and EXPLAIN ANALYZE > output would help people provide more specific feedback. Seemed a general enough question that it wasn't necessary. That, and I wanted to figure out as much o

Re: [GENERAL] Speeding up index scans by truncating timestamp?

On 15 Feb 2011, at 16:20, Vick Khera wrote: > On Tue, Feb 15, 2011 at 10:00 AM, Derrick Rice wrote: >> Is the query optimizer capable of using the relationship between an index on >> date_trunc(foo) and a query with "where foo < bar and foo > baz" ? At this >> point the question is to satisfy my

Re: [GENERAL] finding bogus UTF-8

On Thu, Feb 10, 2011 at 9:02 PM, Scott Ribe wrote: > I know that I have at least one instance of a varchar that is not valid > UTF-8, imported from a source with errors (AMA CPT files, actually) before > PG's checking was as stringent as it is today. Can anybody suggest a query to > find such v

Re: [GENERAL] finding bogus UTF-8

On Tue, Feb 15, 2011 at 11:09 AM, Geoffrey Myers wrote: > comments would be appreciated. > If all you're doing is filtering stdin to stdout and deleting a range of characters, it seems that tr would be a faster tool: cat foo.txt | tr -d '\000-\008\013-\037\177-\377' > foo-cleaned.txt -- Sent v

Re: [GENERAL] pg_dump: schema with OID 58698 does not exist

On Fri, Feb 11, 2011 at 03:17:51PM -0500, Tom Lane wrote: - David Kerr writes: - > So i removed the 5 entries from pg_class, but i still get that error - > when trying to pg_dump: - - > pg_dump: schema with OID 58698 does not exist - - > Any other ideas where i could look? - - Well, it could b

Re: [GENERAL] Best RDB book to suggest

Thanks for your advices :) 2011/2/15 Dean Gibson (DB Administrator) > On 2011-02-15 10:39, Alpha Beta wrote: > >> Hi list, >> >> As there's many developpers of databases in this list, I thought maybe >> someone can suggest me one of the best relational database books and that >> explain in a dee

[GENERAL] subset of attributes

Hi, while reading about databases, I didn't understand what can be subset of attributes, see the following : Let Ri (Xi) be a relation sheme, where Xi is a set of attributes. and if t is a tuple for Ri (Xi) and Y is a subset of Xi, then t[Y] denote the subtuple of t corresponding to Y. does a s

Re: [GENERAL] finding bogus UTF-8

Vick Khera wrote: On Tue, Feb 15, 2011 at 11:09 AM, Geoffrey Myers wrote: comments would be appreciated. If all you're doing is filtering stdin to stdout and deleting a range of characters, it seems that tr would be a faster tool: cat foo.txt | tr -d '\000-\008\013-\037\177-\377' > foo-clea

Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL

On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin wrote: > > > 2011/2/15 Merlin Moncure >> >> On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin >> wrote: >> > 2011/2/14 Merlin Moncure >> >> >> >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson >> >> >> >> wrote: >> >> > Dear PostgreSQL communit

[GENERAL] help understanding explain output

Hello, I've got a doubt about partial indexes and the path chosen by the optimizer. Consider this simple scenario: CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY KEY (pk) ); INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,100), 'val1b', 'val2b', true

[GENERAL] PostgresSQL 9.0 64 bit on windows 2008 64bit

Hi We currently use postgres 8.3 on windows 2008 32bit With max_connections set to 1000 Since we have more than 125 connections, we run postgres as a stand alone process instead of as a service. http://wiki.postgresql.org/wiki/Running_&_Installing_PostgreSQL_On_Native_Windows#I_cannot_run_with_mor

Re: [GENERAL] subset of attributes

On 02/15/11 2:01 PM, Alpha Beta wrote: Hi, while reading about databases, I didn't understand what can be subset of attributes, see the following : Let Ri (Xi) be a relation sheme, where Xi is a set of attributes. and if t is a tuple for Ri (Xi) and Y is a subset of Xi, then t[Y] denote th

Re: [GENERAL] help understanding explain output

Le 15/02/2011 15:49, Luca Ferrari a écrit : > Hello, > I've got a doubt about partial indexes and the path chosen by the optimizer. > Consider this simple scenario: > > CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY > KEY (pk) ); > INSERT INTO p(pk, val1, val2, b) V

Re: [GENERAL] help understanding explain output

On 16/02/11 01:49, Luca Ferrari wrote: Hello, I've got a doubt about partial indexes and the path chosen by the optimizer. Consider this simple scenario: CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY KEY (pk) ); INSERT INTO p(pk, val1, val2, b) VALUES( generate_se

Re: [GENERAL] help understanding explain output

Guillaume Lelarge writes: > Le 15/02/2011 15:49, Luca Ferrari a écrit : >> So a sequential scan. I know that the optimizer will not consider an index >> if >> it is not filtering, but I don't understand exactly why in this case. > Accessing a page in an index is way costier then accessing a pag

Re: [GENERAL] subset of attributes

Some context would help but some (possibly relevant) possibilities: 1: If you have relation with 5 attributes you can generate tuples with all 5 attributes OR you can generate tuples with a subset of those attributes. Seems obvious but often these kinds of definitions are. 2: When looking a

[GENERAL] question regarding full_page_write

I can't clearly understand what FULL_PAGE_WRITE parameter is stand for. Documentation suggest that If I make it OFF, then I have the chance for DB crash. Can anyone please tell me how it could be happened?

Re: [GENERAL] help understanding explain output

Luca Ferrari wrote: Hello, I've got a doubt about partial indexes and the path chosen by the optimizer. Consider this simple scenario: CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY KEY (pk) ); INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,100),

Re: [GENERAL] PostgresSQL 9.0 64 bit on windows 2008 64bit

On Tue, Feb 15, 2011 at 12:50, Niklas Langvig wrote: > Hi > > We currently use postgres 8.3 on windows 2008 32bit > > With max_connections set to 1000 Wow. That's really not good for performance on Windows.. > Since we have more than 125 connections, we run postgres as a stand alone > process i