[GENERAL] OFFSET and LIMIT - performance
Hello, I'm using PGDB with JDBC. In my app i need to select only portion of all available rows. I know i can do it two ways: 1. I can use OFFSET and LIMIT SQL statements or 2. I can select all rows and then filter requested portion in Java. My question - Does the second way significantly affect performance especially when used with JDBC? Does the LIMIT statement do anything else then browsing/filtering rows with cursor (= the same thing i'd do in Java)? How are the selected rows processed in select queries? Does PG select all rows and then filter them using statements like LIMIT and OFFSET or applies the statements while processing query? Thanks for your analyzes! JB
Re: [GENERAL] JDBC - Prepared statements and PostgreSql Time/Date operations
Got it! "Jan Bilek" <[EMAIL PROTECTED]> writes: I would like to use this query in java PreparedStatement, where age of a = row would be one of its parameters: PreparedStatement could look like this: select * from mytable where creation_time > (CURRENT_TIMESTAMP - ?) But nothing works with PreparedStatements! I tried to set whole interval strings "interval '7 days'" as a parameter I tried to set time strings '7 days' as a parameter I tried to use BigDecimal as a parameter... I'm not sure that Java has a type corresponding to "interval". Instead use number-times-interval multiplication: ... creation_time > (CURRENT_TIMESTAMP - ? * interval '1 day') Integer-interval multiplication is the way!!! Many thanks for quick help. (Java seems not to have oracle/postgre interval type) The parameter is now of plain integer or float type. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] JDBC - Prepared statements and PostgreSql Time/Date operations
Hello, I've got following problem: I use this simple query: select * from mytable where creation_time > (CURRENT_TIMESTAMP - interval '7 days'); ---> it selects all rows from mytable, which were created before one week or sooner (creation_time is column in mytable). I would like to use this query in java PreparedStatement, where age of a row would be one of its parameters: PreparedStatement could look like this: select * from mytable where creation_time > (CURRENT_TIMESTAMP - ?) But nothing works with PreparedStatements! I tried to set whole interval strings "interval '7 days'" as a parameter I tried to set time strings '7 days' as a parameter I tried to use BigDecimal as a parameter... So, my question is: how to dynamically set time interval values to PreparedStatements and how to apply them to my example? I've searched PostgreSql and Java docs, but i haven't find any cure. Thanks for help. JB
[GENERAL] JDBC - setting PG variables in URL
Hi, Is it possible to set any of postgre variables in JDBC conection URL? Example: "jdbc:postgresql://localhost:/dbname?pgvar1=value2&pgvar2=value2" Is this or simular construction plausible? We tried it, but it didn't work. OR - question could be put this way: How to set internal postgre variables (like search_path etc.) from Java2 enviroment using JDBC? Thanks for any hints. Regards, Jan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Any "guide to indexes" exists?
I was asking for these "specific/particular reasons". Im not the database developer, to be expert on indexes (i know whats btree and hash - gin and gist are rather mysterious for me). Ok - btree is fine, but sometimes could be better to use gist - my question is: when is that "sometimes"? Unless i know how to use the indexes, then they are useless for me - am i right? Note: We are using gin with tsearch2 vectors, but here we had no choice - tsearch2 works only with gin and gist. Regards, Jan Am Montag, 7. Mai 2007 16:09 schrieb Jan Bilek: I would need more info about index types in postgre (btree, hash, gin and gist) - is there any guide that explains in detail when to use which index type? You use btree unless you have a specific, particular reason to use one of the other ones. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Any "guide to indexes" exists?
Hello, I would need more info about index types in postgre (btree, hash, gin and gist) - is there any guide that explains in detail when to use which index type? These index types have different performance with certain collumn types and data characteristics store in them. There's not much info about it pg docs. So, does any document describing detailed index usage or do you have any personal recomendations when to use which index? Thanks for you answers. Regards, Jan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Password authentication failed
Connect to PostgreSql as Postgres user (default database user): psql yourdb -U Postgres then you will be asked for password selected during the installation. Hope this will help. JB - Original Message - From: Suresh Nimbalkar To: pgsql-general@postgresql.org Sent: Tuesday, May 01, 2007 1:29 PM Subject: [GENERAL] Password authentication failed Hi! I am a complete newbee to Postgres. Have installed Postgres on Windows 2003 server SP1 a week back. When I try to log-in to the server (by writting psql mydb at command prompt in postgres/bin directory), I keep getting a message "psal: FATAL: password authentication failed for user"Administrator". I have installed Postgres as an Administrator and log-in to the server as administrator. I don't think I am making mistake in entering the password. It's quite frustrating. Will someone please help? Thanks and regards Vedsur -- Ahhh...imagining that irresistible "new car" smell? Check out new cars at Yahoo! Autos.
Re: [GENERAL] PostgreSql replication and load balancing ( is Slony-I a solution?)
On 5/1/07, Jan Bilek <[EMAIL PROTECTED]> wrote: Is Slony-I capable of load balancing and how to set it up? We searched the web and some people mentioned that Slony-I could do load balancing, but haven't found how to make Slony-I to do it. Slony does not do load balancing. Personally, I recommend doing the load balancing in the application if possible; you will need to funnel updates to the master and distribute queries to the slaves, and the app is in the best position to determine whether something is an update or a query. Any intermediate middleware is going to add additional latency and overhead and will add just another single point of failure to an already complex system. Alexander. Thanks for info. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSql replication and load balancing ( is Slony-I a solution?)
Hello, We're building database system with replication. Slony-I seems to be a quite good solution for the replication, but beside the replication (master-to-multiple slaves), we need load balancing aswell - multiple users will access the database at the same time=multiple queries. Is Slony-I capable of load balancing and how to set it up? We searched the web and some people mentioned that Slony-I could do load balancing, but haven't found how to make Slony-I to do it. Simple applications like pgpool could do load balancing, too, but they seem to be too simple to be used as replication system. Thanks for any help. J. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
