[PERFORM] Cost of INSERT rules
Hi folks, I really like the idea of exclusively using views as interfaces to the applications (each app has its own view, tuned for the app's access patterns). So, in my model, of course inserts and updates also happen through views, also more complex operations could be triggered through view writes. But for some strange reason, my apps seem to produce high load on the server. So either I'm just too stupid for proper schema design or the view approach is really slow. What exactly does happen behind the scenes if I update some row in a view ? Does it end up in an full view query before doing the update ? cu -- - Enrico Weigelt== metux IT service - http://www.metux.de/ - Please visit the OpenSource QM Taskforce: http://wiki.metux.de/public/OpenSource_QM_Taskforce Patches / Fixes for a lot dozens of packages in dozens of versions: http://patches.metux.de/ - -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Performance on writable views
Hi folks, I'm often using writable views as interfaces to clients, so they only see virtual objects and never have to cope with the actual storage, ie. to give some client an totally denormalized view of certain things, containing only those information required for certain kind of operations. This method is nice for creating easy and robust client interfaces - internal schema changes are not visible to the client. In situations when many, many clients - often coded/maintained by different people - have to access an database which is still under development (typical for many inhouse applications), it helps to circument interface instabilities. Now I've got the strange feeling that this makes updates slow, since it always has to run the whole view query to fetch an record to be updated (ie. to get OLD.*). Could anyone with some deep insight please give me some details about that issue ? cu -- - Enrico Weigelt== metux IT service - http://www.metux.de/ - Please visit the OpenSource QM Taskforce: http://wiki.metux.de/public/OpenSource_QM_Taskforce Patches / Fixes for a lot dozens of packages in dozens of versions: http://patches.metux.de/ - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Implementing an regex filter
) with time zone) Total runtime: 126921.854 ms I'm not sure what Total runtime means. Is it the time the analyze took or the query will take to execute ? If it's really the execution time, then the second query would be much faster (about 2mins vs. 18mins). But I really wonder, why is processing one huge regex so dramatically slow ? BTW: in some tables I'm using the username instead (or parallel to) the numerical id to skip joins against the user table. But I'm not sure if this wise for performance. Any hints for futher optimization appreciated :) thx -- - Enrico Weigelt== metux IT service - http://www.metux.de/ - Please visit the OpenSource QM Taskforce: http://wiki.metux.de/public/OpenSource_QM_Taskforce Patches / Fixes for a lot dozens of packages in dozens of versions: http://patches.metux.de/ - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Index + mismatching datatypes [WAS: index on custom function; explain]
* Yann Michel [EMAIL PROTECTED] wrote: 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 I've got a similar problem: I have to match different datatypes, ie. bigint vs. integer vs. oid. Of course I tried to use casted index (aka ON (foo::oid)), but it didn't work. What am I doing wrong ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgreSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Massive delete performance
* Andy [EMAIL PROTECTED] wrote: snip I have the following problem: I have a client to which we send every night a dump with a the database in which there are only their data's. It is a stupid solution but I choose this solution because I couldn't find any better. The target machine is a windows 2003. So, I have a replication only with the tables that I need to send, then I make a copy of this replication, and from this copy I delete all the data's that are not needed. Why not filtering out as much unnecessary stuff as possible on copying ? snip How can I increase this DELETE procedure because it is really slow??? There are of corse a lot of data's to be deleted. Have you set up the right indices ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgreSQL :)) http://www.fxignal.net/ - ---(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: [PERFORM] plain inserts and deletes very slow
* Klint Gore [EMAIL PROTECTED] wrote: snip Turn on statement logging. I've seen delphi interfaces do extra queries on system tables to find some structure information. I'm already using statement logging of all queries taking longer than 200ms. It seems that only the INSERT takes such a time. The client is in fact written in delphi, and it sometimes seems to do strange things. For example we had the effect, that some new fields in some table were regularily NULL'ed. None of the triggers and rules inside the DB could do that (since there's no dynamic query stuff) and the delphi application is the only one writing directly to this table. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Select performance vs. mssql
* Bruno Wolff III [EMAIL PROTECTED] wrote: snip This gets brought up a lot. The problem is that the index doesn't include information about whether the current transaction can see the referenced row. Putting this information in the index will add significant overhead to every update and the opinion of the developers is that this would be a net loss overall. wouldn't it work well to make this feature optionally for each index ? There could be some flag on the index (ie set at create time) which tells postgres whether to store mvcc information. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Need help to decide Mysql vs Postgres
* PFC [EMAIL PROTECTED] wrote: snip For Python it's the reverse : the MySQL driver is slow and dumb, and the postgres driver (psycopg 2) is super fast, handles all quoting, and knows about type conversions, it will automatically convert a Python List into a postgres Array and do the right thing with quoting, and it works both ways (ie you select a TEXT[] you get a list of strings all parsed for you). It knows about all the postgres types (yes even numeric = python Decimal) and you can even add your own types. That's really cool, plus the developer is a friendly guy. Is there anything similar for java ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Why the planner is not using the INDEX .
* David Gagnon [EMAIL PROTECTED] wrote: FOR inventoryTransaction IN SELECT DISTINCT IRNUM, IRAENUM, IRSTATUT, IRSENS, IRSOURCE, IRDATE, IRQTE FROM IR WHERE IRNUM = ANY (requestIds) and IRYPNUM = companyId LOOP hmm. you probably could create the query dynamically and then execute it. BTW: why isn't IN not usable with arrays ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] plain inserts and deletes very slow
* David Mitchell [EMAIL PROTECTED] wrote: Hi, Hmm, you said you don't experience this when executing the query manually. What adapter are you using to access postgres from your application? libpq, npgsql or something else? huh, its a delphi application ... (I didnt code it). And what is your method for running the query 'manually'. Are you running it locally or from a remote machine or what? using psql remotely - database and client machines are sitting on the same wire. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] plain inserts and deletes very slow
* Alvaro Herrera [EMAIL PROTECTED] wrote: On Mon, Jul 04, 2005 at 02:17:47AM +0200, Enrico Weigelt wrote: * David Mitchell [EMAIL PROTECTED] wrote: Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 minutes would be closer to your mark. Try vacuuming every 15 minutes for a start and see how that affects things (you will have to do a vacuum full to get the tables back into shape after them slowing down as they have). hmm. I've just done vacuum full at the moment on these tables, but it doesnt seem to change anything :( Maybe you need a REINDEX, if you have indexes on that table. Try that, coupled with the frequent VACUUM suggestion. I've tried it, but it doesn't seem to help :( cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] investigating slow queries through pg_stat_activity
* Dan Harris [EMAIL PROTECTED] wrote: Hi, I've got some queries generated by my application that will, for some reason, run forever until I kill the pid. Yet, when I run the queries manually to check them out, they usually work fine. If you can change your application, you could try to encapsulate the queries into views - this makes logging and tracking down problems much easier. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] plain inserts and deletes very slow
Hi folks, my application reads and writes some table quite often (multiple times per second). these tables are quite small (not more than 20 tuples), but the operations take quite a long time (300 ms!). The query operations are just include text matching (=) and date comparison (,). I wasn't yet able to track down, if all these queries take sucha long time or just sometimes. When running them manually or trying explain, evrything's fast. Probably there could be some side effects with other concurrent quries. Could anyone give me advice ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] plain inserts and deletes very slow
* Steinar H. Gunderson [EMAIL PROTECTED] wrote: On Mon, Jul 04, 2005 at 12:45:37AM +0200, Enrico Weigelt wrote: my application reads and writes some table quite often (multiple times per second). these tables are quite small (not more than 20 tuples), but the operations take quite a long time (300 ms!). Are you VACUUMing often enough? I've just VACUUM'ed multiple times, so it's perhaps not the problem. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] plain inserts and deletes very slow
* Enrico Weigelt [EMAIL PROTECTED] wrote: forgot to mention: + linux-2.6.9 + postgres-7.4.6 + intel celeron 2ghz + intel ultra ata controller + 768mb ram cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: 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: [PERFORM] plain inserts and deletes very slow
* David Mitchell [EMAIL PROTECTED] wrote: Did you vacuum full? When you do lots of inserts and deletes, dead tuples get left behind. When you vacuum, postgres will reuse those dead tuples, but if you don't vacuum for a long time these tuples will build up lots. Even when you vacuum in this case, the dead tuples are still there, although they are marked for reuse. Vacuuming full actually removes the dead tuples. I'm doing a VACUUM ANALYZE every 6 hours. vacuum'ing manually doesnt seem to have any effect on that. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: 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: [PERFORM] plain inserts and deletes very slow
* David Mitchell [EMAIL PROTECTED] wrote: Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 minutes would be closer to your mark. Try vacuuming every 15 minutes for a start and see how that affects things (you will have to do a vacuum full to get the tables back into shape after them slowing down as they have). hmm. I've just done vacuum full at the moment on these tables, but it doesnt seem to change anything :( cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] BLOB's bypassing the OS Filesystem for better Image loading speed?
* [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, My next queststion is dedicated to blobs in my Webapplication (using Tomcat 5 and JDBC integrated a the J2EE Appserver JBoss). Filesystems with many Filesystem Objects can slow down the Performance at opening and reading Data. As others already pointed out, you probably meant: overcrowded directories can make some filesystems slow. For ext2 this is the case. Instead reiserfs is designed to handle very large directories (in fact by using similar indices like an database does). If your application is an typical web app your will probably have the situation: + images get read quite often, while they get updated quite seldom. + you dont want to use image content in quries (ie. match against it) + the images will be transfered directly, without further processing + you can give the upload and the download-server access to a shared filesystem or synchronize their filesystems (ie rsync) Under this assumptions, I'd suggest directly using the filesystem. This should save some load, ie. + no transfer from postgres - webserver and further processing (server side application) necessary, the webserver can directly fetch files from filesystem + no further processing (server side application) necessary + backup and synchronization is quite trivial (good old fs tools) + clustering (using many image webservers) is quite trivial Already mentioned that you've got to choose the right filesystem or at least the right fs organization (ie. working with a n-level hierachy to keep directory sizes small and lookups fast). An RDBMS can do this for you and so will save some implementation work, but I don't think it will be noticably faster than an good fs-side implementation. Of course there may be a lot of good reasons to put images into the database, ie. if some clients directly work on db connections and all work (including image upload) should be done over the db link. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] index on different types
Hi folks, there's often some talk about indices cannot be used if datatypes dont match. On a larger (and long time growed) application I tend to use OID for references on new tables while old stuff is using integer. Is the planner smart enough to see both as compatible datatype or is manual casting required ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: 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
[PERFORM] index not used
Hi folks, I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: id bigint primary key, a varchar, b varchar, c varchar and I'm quering: select * from foo where id = 2; I've got only 15 records in this table, but I wanna have it as fast as possible since its used (as a map between IDs and names) for larger queries. thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] immutable functions vs. join for lookups ?
* Jaime Casanova [EMAIL PROTECTED] wrote: snip Even if your data never changes it *can* change so the function should be at most stable not immutable. okay, the planner sees that the table could potentionally change. but - as the dba - I'd like to tell him, this table *never* changes in practise (or at most there will be an insert once a year) isnt there any way to enforce the function to be really immutable ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] immutable functions vs. join for lookups ?
* Tom Lane [EMAIL PROTECTED] wrote: snip Yeah, I was actually thinking about a two-step process: inline the function to produce somethig equivalent to a handwritten scalar sub-SELECT, and then try to convert sub-SELECTs into joins. ... back to my original question ... What kind of query should I use ? Is a join better than a function ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] foreign key performance
Hi folks, do foreign keys have any influence on performance (besides slowing down huge inserts) ? do they bring any performance improvement ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] immutable functions vs. join for lookups ?
* Tom Lane [EMAIL PROTECTED] wrote: Enrico Weigelt [EMAIL PROTECTED] writes: c) CREATE FUNCTION id2username(oid) RETURNS text LANGUAGE 'SQL' IMMUTABLE AS ' SELECT username AS RESULT FROM users WHERE uid = $1'; This is simply dangerous. The function is *NOT* immutable (it is stable though). When ... not if ... your application breaks because you got the wrong answers, you'll get no sympathy from anyone. In my case it is immutable. The username never changes. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(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
Re: [PERFORM] clear function cache (WAS: SQL function inlining)
* Stephan Szabo [EMAIL PROTECTED] wrote: On Thu, 24 Mar 2005, Enrico Weigelt wrote: * Alvaro Herrera [EMAIL PROTECTED] wrote: On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote: BTW: is it possible to explicitly clear the cache for immutable functions ? What cache? There is no caching of function results. Not ? So what's immutable for ? For knowing that you can do things like use it in a functional index and I think for things like constant folding in a prepared plan. So when can I expect the function to be reevaluated ? Next query ? Next session ? Random time ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] immutable functions vs. join for lookups ?
Hi folks, I like to use (immutable) functions for looking up serveral (almost constant) things, i.e fetching a username by id. This makes my queries more clear. But is this really performant ? Lets imagine: We've got an table with user accounts (uid,name,...). Then we've got another one which contains some items assigned to users, and so are linked to them by an uid field. Now want to view the items with usernames instead of just uid: a) SELECT items.a, items.b, ..., users.username FROM items, users WHERE items.uid = users.uid; c) CREATE FUNCTION id2username(oid) RETURNS text LANGUAGE 'SQL' IMMUTABLE AS ' SELECT username AS RESULT FROM users WHERE uid = $1'; SELECT items.a, items.b, ..., id2username(users.uid); Which one is faster with a) only a few users (50) b) many users ( 1k ) while we have several 10k of items ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] clear function cache (WAS: SQL function inlining)
* Tom Lane [EMAIL PROTECTED] wrote: big_snip BTW: is it possible to explicitly clear the cache for immutable functions ? I'd like to use immutable functions for really often lookups like fetching a username by uid and vice versa. The queried tables change very rarely, but when they change is quite unpredictable. thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] View columns calculated
* Tom Lane [EMAIL PROTECTED] wrote: Peter Darley [EMAIL PROTECTED] writes: I have a question about views: I want to have a fairly wide view (lots of columns) where most of the columns have some heavyish calculations in them, but I'm concerned that it will have to calculate every column even when I'm not selecting them. So, the question is, if I have 5 columns in a view but only select 1 column, is the system smart enough to not calculate the unused columns, It depends on what the rest of your view looks like. If the view is simple enough to be flattened into the parent query then the unused columns will disappear into the ether. If it's not flattenable then they will get evaluated. You can check by seeing whether an EXPLAIN shows a separate subquery scan node corresponding to the view. (Without bothering to look at the code, an unflattenable view is one that uses GROUP BY, DISTINCT, aggregates, ORDER BY, LIMIT, UNION, INTERSECT, EXCEPT, probably a couple other things.) What about functions ? I'm using several (immutable) functions for mapping IDs to names, etc. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] clear function cache (WAS: SQL function inlining)
* Alvaro Herrera [EMAIL PROTECTED] wrote: On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote: BTW: is it possible to explicitly clear the cache for immutable functions ? What cache? There is no caching of function results. Not ? So what's immutable for ? snip I'd like to use immutable functions for really often lookups like fetching a username by uid and vice versa. The queried tables change very rarely, but when they change is quite unpredictable. Maybe you should use a stable function if you fear we'll having function result caching without you noticing. hmm, this makes more real evaluations necessary than w/ immuatable. AFAIK stable functions have to be evaluated once per query, and the results are not cached between several queries. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
