General developer forum

I think it's worth fixing. The difference is low in numbers, but high in percentage. So at the end of the day, and over thousand of DB queries, it is huge.

In my trivial example below, the difference is more than double (from 3502.21..13503.20 to 7010.62..34427.98 estimated cost). See the query plan with and without a forced cast:

op_18moodle=> explain select u.username from mdl_user u join mdl_user_preferences p on p.userid=u.id;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Hash Join  (cost=3502.21..13503.20 rows=250333 width=15)
   Hash Cond: (p.userid = u.id)
   ->  Seq Scan on mdl_user_preferences p  (cost=0.00..4994.33 rows=250333 width=4)
   ->  Hash  (cost=2837.65..2837.65 rows=53165 width=19)
         ->  Seq Scan on mdl_user u  (cost=0.00..2837.65 rows=53165 width=19)
(5 rows)

op_18moodle=> explain select u.username from mdl_user u join mdl_user_preferences p on p.userid=u.id::bigint ;
                                                               QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=7010.62..34427.98 rows=490472 width=15)
   Merge Cond: (p.userid = "inner"."?column3?")
   ->  Index Scan using mdl_user_preferences_useridname_idx on mdl_user_preferences p  (cost=0.00..17582.44 rows=250333 width=4)
   ->  Sort  (cost=7010.62..7143.53 rows=53165 width=19)
         Sort Key: (u.id)::bigint
         ->  Seq Scan on mdl_user u  (cost=0.00..2837.65 rows=53165 width=19)
(6 rows)

And actually running them with explain analyse shows it bears out

 No cast
 Total runtime: 1134.926 ms

 Cast to bigint
 Total runtime: 2457.717 ms

So there you go - bring a big axe, and chop your DB load in half.

perhaps we could normalize the situation before 2.0.

Great idea!

I think it needs to be an optional script, commandline (I'd do it in perl, but the natural thing is to do it in PHP), full of "backup your DB" warnings that bipasses complexities of dancing with XMLDB and friends and does whatever needs to be done for each DB.

Small self-hosted moodles might not see or care about the impact or want to deal with the complexities of this. But we need it in the arsenal for larger DBs.

I think that there was some limitation under PostgreSQL 7.4

IIRC you are right. No prob - PKs are not magic in Pg, we can add new col, copy data, drop old col, rename newcol to id, recreate sequences & indexes. Nothing that a for loop and some SQL cannot resolve

I think might be the same in Oracle.

Maybe I saw a few ints that I didn't expect to see. But you are right -- things that are clearly smallints (visible, etc) weren't converted. I do think we have a few ints that could be smallints. Small fry really.

And yeah, the SQL it proposed killed my laptop for about 2hs. Ha ha ha. And the DB grew a lot - I didn't measure it, but wanted to warn people

Finally, no. There isn't any option to pass special flags to DDL statements.

I thought so. And I agree it's a bad idea. However, my favourite DB needs one for performance.

as far as I know, LIKE statements (not ILIKE ones), using righted wildcards should work perfectly, no matter of the LOCALE being used

I was saying the same until 24hs ago - but I have just learned that it's not true. Did an EXPLAIN on a query and found it was scanning the field. A bit of googling found this

"The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries."

http://www.postgresql.org/docs/8.1/static/indexes-types.html

And when I recreated the index with the varchar_pattern_ops "magic option" the problem was fixed.

I do wonder why Pg doesn't do it by default? There may be an additional cost to maintaining it?