PostgreSQL @ LinuxDays Chemnitz

Hi all,

I'm back from Chemnitz, the german PG User Group was there with a booth, a workshop (performance tuning) and a talk about the upcoming 9.0-features.

The workshop and the talk was given by ads, so i think, he will write a blog-posting too. But i can say there was a LOT of attendees to listen his talk.

From my point of view i can say it was a success, many people comes to us and ask us 'how can we migrate from database XYZ to PostgreSQL?'. Great!

And yes, we had also a LOT of happy pg-users and we had heard much positive stories about PG.

But one disadvantage with PG comes up, particular in a hosting environment: every user can see all databases. I had heard that more than once, maybe we should think about a switch in postgresql.conf to disable this.


Regards.

JOIN REMOVAL - a short performance test

As i said yesterday, I'm back from FOSDEM where I have learned a LOT about our new version 9.0. For instance: join removal.

Explanation:

- Implement "join removal" for cases where the inner side of a left
  join is unique and is not referenced above the join.  In this case
  the inner side doesn't affect the query result and can be thrown
  away entirely.  Although perhaps nobody would ever write such a
  thing by hand, it's a reasonably common case in machine-generated
  SQL.

(copied from PostgreSQL Weekly News - September 20 2009)

Let's make a short performance test:

First I have created 2 tables:

test=# \d table_a
    Table "public.table_a"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
 value  | text    |
Indexes:
    "table_a_pkey" PRIMARY KEY, btree (id)

test=# \d table_b
    Table "public.table_b"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 value  | text    |

Let me populate this 2 tables, using another nice new feature: writeable CTE:

test=# with a as 
          (insert into table_a select s, md5(s::text) from generate_series(0,250000) s returning *), 
     b as                                                                                            
          (insert into table_b select id, md5(value) from a where substring(value,1,1) between '0' and '9') 
select true;                                                                                                
 bool                                                                                                       
------                                                                                                      
 t                                                                                                          
(1 row)                                                                                                     

Writeable CTE is a nice feature, isn't it? I love it ;-)

Okay. Now i have 2 populated tables. I have analysed this tables and I have created a VIEW:

test=*# create view view_table_ab as select a.id, a.value as value_a, b.value as value_b from table_a a left join table_b b on a.id=b.id;
CREATE VIEW                                                                                                                              

Okay, first test case without an index on table_b.id:

test=*# explain analyse select id, value_a from view_table_ab;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=7532.53..28639.73 rows=250001 width=37) (actual time=183.941..603.520 rows=250001 loops=1)            
   Hash Cond: (a.id = b.id)                                                                                                  
   ->  Seq Scan on table_a a  (cost=0.00..12917.01 rows=250001 width=37) (actual time=36.073..125.789 rows=250001 loops=1)   
   ->  Hash  (cost=5047.68..5047.68 rows=156068 width=4) (actual time=147.752..147.752 rows=156068 loops=1)                  
         ->  Seq Scan on table_b b  (cost=0.00..5047.68 rows=156068 width=4) (actual time=0.019..72.371 rows=156068 loops=1) 
 Total runtime: 661.780 ms                                                                                                   
(6 rows)                                                                                                                     

Okay, as expected an seq-scan on table b. The plan doesn't change even if I create a non-unique index on table_b(id):

test=*# create index idx_table_b_id on table_b(id);
CREATE INDEX
Time: 137,121 ms
test=*# explain analyse select id, value_a from view_table_ab;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=5346.53..18120.73 rows=250001 width=37) (actual time=141.921..548.674 rows=250001 loops=1)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on table_a a  (cost=0.00..4584.01 rows=250001 width=37) (actual time=0.006..84.054 rows=250001 loops=1)
   ->  Hash  (cost=2861.68..2861.68 rows=156068 width=4) (actual time=141.856..141.856 rows=156068 loops=1)
         ->  Seq Scan on table_b b  (cost=0.00..2861.68 rows=156068 width=4) (actual time=0.005..66.048 rows=156068 loops=1)
 Total runtime: 607.385 ms
(6 rows)

Okay, let me drop this index and recreate it again, but with the unique-option, and re-run the select:

test=*# drop index idx_table_b_id;
DROP INDEX
Time: 16,038 ms
test=*# create unique index idx_table_b_id on table_b(id);
CREATE INDEX
Time: 122,602 ms
test=*# explain analyse select id, value_a from view_table_ab;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on table_a a  (cost=0.00..4584.01 rows=250001 width=37) (actual time=0.007..74.825 rows=250001 loops=1)
 Total runtime: 130.887 ms
(2 rows)

Time: 131,348 ms

Nice. Well, I think, this feature isn't a big thing. The new version 9.0 contains a LOT of really big things, this one is just a optimisation. But it works and it is useful. Thanks to Robert Haas and Tom Lane for this feature.


Andreas

Back from FOSDEM

Hi all,

I'm back from FOSDEM, Brussels. It was a great and impressive event and i meet there a lot of PostgreSQL-people. The talks given in our devroom were imposing, i have learned a LOT about our upcoming new release 9.0, in particular streaming replication, exclusion constraints and (with thanks to the talk given from Sergey Petrunya about MariaDB) join removal (the MariaDB calls this feature "table elimination" and after his talk i have discussed that feature with Stefan 'mastermind' Kaltenbrunner. He informed me that we have this feature in 9.0, but we called it 'join removal').


Okay, i don't want peeve you with my really poor english. Last but not least, i have made a lot of pictures. You can see almost all pictures here:

http://a-kretschmer.de/bruessel/

Not all pictures are in a reasonable quality, sorry. The pictures are reduced to 50%, if someone want individual pictures in full resolution, just ask me.


I'm looking forward to see you in Amsterdam...