[SQL] defining and using constants in postgreSQL
Hello, I am looking for a way that I can define a constant in PostgreSQL (so that I end up with a constant similar to one that I could have in Oracle). I have searched the archives and it seems that the solutions that were suggested in include creating a table of constants or alternatively creating a function that returns a constant. However, I would like to have a predefined constant in the global scope which I can access without the round brackets used to call a function. That is, I would like to be able to define a constant named foo that I can then access with the name foo, not foo(). Is this possible at all with PostgreSQL? I know PostgreSQL has some predefined constants that don't take the round brackets so in principle it should be possible to define more. Is there a way to do such thing in PostgreSQL? Thanks, Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] index and min()
Hello, I have a table that contains logs from a radius accounting server. Currently the table contains 1,780,470 rows and is about 350Mb on the disk. It is running on a mssql server at the moment but I would like to move it over to a postgresql server and postgresql seems to be somewhat fast than mssql on most things except one. SELECT min(inserttime) FROM acc_pb; which takes about 11 seconds to complete. On the mssql server this takes less than 1 second. On the mssql server the inserttime column has a cluster index which if I understand correctly means that the index is ordered which would explain why the min() and max() functions are fast. Is there an ordered index on postgresql or is the problem something else? I have a b-tree index on the inserttime column in postgresql but it isn't used somehow. test=# EXPLAIN ANALYZE SELECT min(inserttime) from accounting; QUERY PLAN Aggregate (cost=55162.88..55162.88 rows=1 width=8) (actual time=11798.27..11798.27 rows=1 loops=1) -> Seq Scan on accounting (cost=0.00..50711.70 rows=1780470 width=8) (actual time=0.14..8703.67 rows=1780470 loops=1) Total runtime: 11798.39 msec (3 rows) Time: 11799.69 ms Any help is greatly appreciated. Best Regards Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] index and min()
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] transmitted: > SELECT min(inserttime) FROM acc_pb; which takes about 11 seconds to > complete. > > On the mssql server this takes less than 1 second. If you have NO index on inserttime, then the best that can be done is the sequential scan that you saw. If there is an index on inserttime, then the PostgreSQL idiom that provides a _massive_ speedup is the query: select inserttime from acc_pb order by inserttime limit 1; In theory, it ought to be a nifty idea to have a way of automatically transforming the min(inserttime) query into what I showed, but that is likely to be difficult to do in general, and nobody has yet proposed an implementation, so you'll have to do that yourself. -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/x.html "Linux and other OSS advocates are making a progressively more credible argument that OSS software is at least as robust -- if not more -- than commercial alternatives." - Microsoft lamenting Open Source Software in the "Halloween Document" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Query Help using Except
On Thu, Oct 23, 2003 at 14:17:08 -0400, [EMAIL PROTECTED] wrote: > > I can't do the following, since the number of selected columns have to match: One option is to use where NOT EXISTS instead of EXCEPT. Another way would be to add A.id to the rows in the set difference using a join. I expect the second method would be slower and that you should try using NOT EXISTS. > > select A.id > , A.charge > , B.user_id > , C.employee_id > from A > inner join B using (user_id) > inner join C using (employee_id) > > except > > select X.charge > , Y.user_id > , Z.employee_id > from X > inner join Y using (user_id) > inner join Z using (employee_id) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Unsubscribe
---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
