[GENERAL] how to enforce index usage with +0
Hi,
In Oracle there are instances when as a developer I know how the data
is spread in the tables and I want to be sure the database uses the
right index. Does the following hold true in Postgresql for a query
like this:
select s.order_id
from small_orders_table s, orders o
where s.find_these_id in
(select some_id from some_table where some_indexed_field = 'ABC')
and s.order_id+0 = o.order_id
and date_trunc('microseconds', o.valid_until_dt) < now()
This should essentially use the s.some_indexed_field as the primary
index and hit the orders table on the order id.
The +0 and date_trunc is used purely to ensure the valid_unit_dt field
(which is indexed) is not used.
Is this efficient?
Does date_trunc render the index invalid or can I do something else
(+0) doesnt work.
thanks
Tim
---(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] which is more scalable for the database?
On Mar 8, 2:26 pm, [EMAIL PROTECTED] (Shane Ambler) wrote: > Timasmithwrote: > > Suppose I have a database table with 20 fields which are lookups to a > > single table. > > > configtable(configtable_id, a_field, something_lookup_id, > > another_lookup_id, ...) > > lookup(lookup_id, value, description, ...) > > > what is going to be faster to map the rows to an Object which needs > > the 'value' for every field ending in lookup_id > > How long is ball of string 'a' compared to ball of string 'b'? > > That will depend on a lot on how large the lookup table will be (100 > rows or 10 million rows?) 100-500,000 rows and on how long your descriptions are. 10-60, perhaps averaging around 20 characters Will > you want all 20 descriptions for each query you run? Yes, it is the price I pay with Hibernate for vastly simplifying data access If your > descriptions are 200 characters each then maybe you can reduce some data > transfer by caching these at the client instead of transferring them > each time you retrieve a result. But then how will you keep your local > cache up to date? The data I would cache would change infrequently in the production environment and there would be no expectation it would be 'immediate' on change. I would provide services to flush on demand, or on save (of reference data), and/or through a scheduler etc. > > I would suggest the view for a couple of reasons - first it will > simplify your queries as you won't need all the join details in every > query you run. Second the select for the query can be parsed and stored > whereas separate selects will be parsed each time. If I was caching on the client, I would select all data only once and there would be no queries, it would only be select * from sometable and the client (actually server side bean), would populate the object with the missing lookups. > > A lot of this will depend on what you are doing and what you are using > to do it. If you are using php then you have less options for caching > than say a client program written in c. php may run pretty fast but it > won't outrun a compiled c program. Java hashtable. > > > > > > > a) View > > > select c.configtable_id, l1.value as something_lookup, l2.value as > > another_lookup > > from configtable c, > >lookup l1, > >lookup l2 > > where c.something_lookup_id = l1.lookup_id > > and c.another_lookup_id = l2.lookup_id > > > foreach row > >map values to object > > end > > > b) Cache all lookup values and populate > > > select c.* from configtable > > > foreach row > >map values to object > >if lookup_id > >find value from hashtable and map value to object > >endif > > end > > > It seems that the latter *might* be better to scale outward better, > > as > > you could add application servers to do the caching/mapping and you > > only select from a single table? > > Maybe but then postgresql may do the lookups quicker than what you have > available at the client end. Right, but could it keep up, how much extra effort is it to do the indexed lookups (almost all in memory), for the client. > > The only way you will really know is to load up some sample data and > test each method yourself. Its very hard to simulate though due to production hardware having 16 cpus, Gigs of memory, huge databases, hundereds of concurrent users - I just dont have access to that kind of environment. > > How far are you expecting to scale? The simplest method may just work > fast enough that all the extra caching and setup/maintenance of this > will outweigh any benefit. I would like to scale to 10,000s of power users on a complex OLTP system. Adding servers to expand out is generally feasible, but one can only scale upward so far before reaching a hardware peak. > > -- > > Shane Ambler > [EMAIL PROTECTED] > > Get Sheeky @http://Sheeky.Biz > > ---(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- Hide quoted text - > > - Show quoted text - ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] which is more scalable for the database?
Suppose I have a database table with 20 fields which are lookups to a single table. configtable(configtable_id, a_field, something_lookup_id, another_lookup_id, ...) lookup(lookup_id, value, description, ...) what is going to be faster to map the rows to an Object which needs the 'value' for every field ending in lookup_id a) View select c.configtable_id, l1.value as something_lookup, l2.value as another_lookup from configtable c, lookup l1, lookup l2 where c.something_lookup_id = l1.lookup_id and c.another_lookup_id = l2.lookup_id foreach row map values to object end b) Cache all lookup values and populate select c.* from configtable foreach row map values to object if lookup_id find value from hashtable and map value to object endif end It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Can I getting a unique ID from a select
On Mar 5, 3:35 am, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > On Sat, Mar 03, 2007 at 16:46:45 -0800, > Timasmith<[EMAIL PROTECTED]> wrote: > > > On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > > > On Thu, Mar 01, 2007 at 06:16:02 -0800, > > > Timasmith<[EMAIL PROTECTED]> wrote: > > > > > create view myview as > > > > select rownum, t1.field, t2.field > > > > from tableOne t1, tableTwo t2 > > > > where t1.key = t2.fkey > > > Never heard of a 'join key' but that sounds very promising. How do I > > select it? > > The join key would be t1.key or t2.fkey from your example. However there > may be multiple rows returned with the same value depending on what you > are joining. If that is the case you, should be able to use the primary > keys of the underlying tables to make a new candidate key for the joined > rows. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend Multiple rows with the same key renders Hibernate useless as it caches the 'row object' and then returns the first row every time for that object. I think the sequence will work though, in reflection I guess it would as fast as pulling another field, and with the numbers would be a very long time before getting duplicates - even if you had thousands of users, returning 100s of rows every few minutes (I think...). ---(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
Re: [GENERAL] Can I getting a unique ID from a select
On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote: > On Thu, Mar 01, 2007 at 06:16:02 -0800, > Timasmith<[EMAIL PROTECTED]> wrote: > > > I am using hibernate, using a view like a read only table and I need a > > primary key each time a select is issued. > > > create view myview as > > select rownum, t1.field, t2.field > > from tableOne t1, tableTwo t2 > > where t1.key = t2.fkey > > > select * from myview > > > But what I really need is > > > select makemeauniquekey, t1.field, t2.field > > ... > > Is there some reason you can't use the join key? > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ Never heard of a 'join key' but that sounds very promising. How do I select it? ---(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] Can I getting a unique ID from a select
I am using hibernate, using a view like a read only table and I need a primary key each time a select is issued. So in Oracle terms this might work, though I am skeptical that Hibernate is going to return a cached result. create view myview as select rownum, t1.field, t2.field from tableOne t1, tableTwo t2 where t1.key = t2.fkey select * from myview But what I really need is select makemeauniquekey, t1.field, t2.field ... Maybe there is no way I think... incrementing a sequence per select is untenable. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL to get a table columns comments?
On Jan 30, 12:15 pm, "codeWarrior" <[EMAIL PROTECTED]> wrote: > SELECT > CASE > WHEN sfl.description IS NOT NULL THEN sfl.description > WHEN sfl.description IS NULL THEN pa.attname::character varying > ELSE pd.description::character varying > END AS label >FROM ONLY pg_class pc >JOIN ONLY pg_attribute pa ON pc.oid = pa.attrelid AND pc.relnamespace = > 2200::oid AND pc.reltype > 0::oid AND (pc.relkind = 'r'::"char" OR > pc.relkind = 'c'::"char" OR pc.relkind = 'v'::"char") >JOIN ONLY pg_type pt ON pa.atttypid = pt.oid >LEFT JOIN ONLY pg_description pd ON pc.oid = pd.objoid AND pa.attnum = > pd.objsubid >LEFT JOIN sys_flex_labels sfl ON pc.oid = sfl.table_oid::oid AND > pa.attname::character varying::text = sfl.column_name::text > WHERE pa.attnum > 0 > ORDER BY pc.relname::character varying, pa.attnum; > > "Timasmith" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > > > > > Hi, > > > What query can I run to get the comments for my table columns. > > > i.e. the ones on my 8.1 database added with this command: > > > COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the > > addresses table'; > > > thanks > > > Tim- Hide quoted text - > > - Show quoted text - I dont know about that query - I dont have sys_flex_labels but this seems to work: select pc.relname as tablename, pa.attname as column, pd.description from pg_description pd, pg_class pc, pg_attribute pa where pc.relowner = 16403 and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum where I had to figure out the relowner and my schema owner ---(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] SQL to get a table columns comments?
Hi, What query can I run to get the comments for my table columns. i.e. the ones on my 8.1 database added with this command: COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the addresses table'; thanks Tim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] postgresql ddl scripts - drop object fails entire script
On the one hand I like how the schema scripts fail when there is a single problem with a DDL statement. On the other hand sometimes it is a pain - especially to take out all the 'drop sequence', 'drop table' etc commands when creating a new database. Is there a 'drop if doesnt exist' or a better way of doing it? thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Upgrading schemas
Hi, I have an application using Postgresql with a large (100+) number of tables. Are there any free/open source tools that can ease the process of updating the schema. Specifically I would like to compare a source and a target, determine the differences and the best way to update the target to be equivalent to the source - tables, columns, views, etc. thanks ---(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] One step install, up and running?
Hi, I have a program which uses Postgresql as a database for persistence. The application uses IzPack to install. Ideally I would like to also install Postgresql in the same wizard conversation, plus get it up and running as a windows service (or daemon on 'nix). Is all of that possible - has anyone done it? thanks! Tim ---(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
