Re: [PERFORM] Having MANY MANY empty columns in database
In a database which we are having we have nearly 100 tables, and in 75% of the tables we have 6 columns ( INT ) as standard columns. What is standard columns, if you create a table in this database you should have some default 6 columns in there they should maintain 1. who is the owner of that read 2. when it is added 3. who is updating the record 4. when it is updated and other columns OK, so your tables all have the same fields (columns), as if you used CREATE TABLE new_table ( LIKE some_template_table ) ? But many of the users are not doing anything with those columns, they are all empty always meaning that they contain NULL values in that field for every record? If you drop those columns we will gain any performance or not. The best way to find that out is to test it. I'd be surprised if it didn't make *some* performance difference, but the question is whether it will be enough to be worth caring about. However, I recall hearing that PostgreSQL keeps a null bitmap and doesn't use any storage for null fields. If that is correct then you probably won't be paying much of a price in disk I/O, but there might still be other costs. I can't help wondering why you have all those useless columns in the first place, and why you have so many identically structured tables. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Having MANY MANY empty columns in database
> > OK, so your tables all have the same fields (columns), as if you used > CREATE TABLE new_table ( LIKE some_template_table ) ? It will contain some other unique columns for each table. > meaning that they contain NULL values in that field for every record? what is the value it may contain i don't know ?? we are not filling any value !! > > > If you drop those columns we will gain any performance or not. > I need to test... HOW to test the overall performance of database.. > However, I recall hearing that PostgreSQL keeps a null bitmap and doesn't > use any storage for null fields. If that is correct then you probably won't > be paying much of a price in disk I/O, but there might still be other costs. > if it is sure that it will not make disk I/O then it is ok > > I can't help wondering why you have all those useless columns in the > first place, and why you have so many identically structured tables. > these are not useless columns... it should be used to update the owner of the record, updated time, created and other stuffs, but nobody is using now. > > -- > Craig Ringer >
[PERFORM] Views and functions returning sets of records
Hi all, maybe it’s a naive question but I was wondering if there is any difference, from a performance point of view, between a view and a function performing the same task, something like: CREATE VIEW foo AS …; CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; Thank you -- Giorgio Valoti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Views and functions returning sets of records
Giorgio Valoti <[EMAIL PROTECTED]> schrieb:
> Hi all,
> maybe it?s a naive question but I was wondering if there is any
> difference, from a performance point of view, between a view and a
> function performing the same task, something like:
>
> CREATE VIEW foo AS ?;
> CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
> SELECT * FROM foo WHERE fooid = $1;
> $$ LANGUAGE SQL;
Yes. The planner can't sometimes optimze the query, a simple example:
I have ha table called 'words', it contains a few thousand simple words.
test=# \d words
Table "public.words"
Column | Type | Modifiers
+--+---
w | text |
Indexes:
"idx_words" btree (lower(w) varchar_pattern_ops)
Now i'm searching and the index is in use:
test=# explain analyse select * from words where lower(w) like lower('foo');
QUERY PLAN
--
Index Scan using idx_words on words (cost=0.00..6.01 rows=1 width=12) (actual
time=0.065..0.065 rows=0 loops=1)
Index Cond: (lower(w) ~=~ 'foo'::character varying)
Filter: (lower(w) ~~ 'foo'::text)
Total runtime: 0.187 ms
(4 rows)
Now i'm writung a function for that:
test=*# create or replace function get_words(text) returns setof record as
$$select * from words where lower(w) like lower($1); $$ language sql;
CREATE FUNCTION
Time: 4.413 ms
The query inside the function body is the same as above, let's test:
test=*# explain analyse select * from get_words('foo') as (w text);
QUERY PLAN
-
Function Scan on get_words (cost=0.00..12.50 rows=1000 width=32) (actual
time=213.947..213.947 rows=0 loops=1)
Total runtime: 214.031 ms
(2 rows)
As you can see, a slow seq. scan are used now. Because the planner don't
know the argument and don't know if he can use the index or not. In my
case the planner created a bad plan.
But a VIEW is not a function, it's only a RULE for SELECT on a virtual table:
test=*# create view view_words as select * from words;
CREATE VIEW
Time: 277.411 ms
test=*# explain analyse select * from view_words where lower(w) like
lower('foo');
QUERY PLAN
--
Index Scan using idx_words on words (cost=0.00..6.01 rows=1 width=12) (actual
time=0.044..0.044 rows=0 loops=1)
Index Cond: (lower(w) ~=~ 'foo'::character varying)
Filter: (lower(w) ~~ 'foo'::text)
Total runtime: 0.259 ms
(4 rows)
It's the same plan as above for the source table.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Views and functions returning sets of records
Giorgio Valoti <[EMAIL PROTECTED]> writes: > maybe its a naive question but I was wondering if there is any > difference, from a performance point of view, between a view and a > function performing the same task, Yes. Usually the view will win. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Views and functions returning sets of records
Tom Lane <[EMAIL PROTECTED]> schrieb: > Giorgio Valoti <[EMAIL PROTECTED]> writes: > > maybe its a naive question but I was wondering if there is any > > difference, from a performance point of view, between a view and a > > function performing the same task, > > Yes. Usually the view will win. *smile* Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
