On Wed, Nov 25, 2009 at 12:39 AM, Domas Mituzas midom.lists@gmail.com wrote:
But.... who will partition based on is_redirect? If it is for one-off task, you can just create two separate tables and do 'manual partitioning' even in sqlite :) Even though your is_redirect queries may become faster, you just added *2 cost for every other index operation (as partitions require you to loop over all indexes for all the lookups not satisfied by partitioning key).
Please read my comment over again: "I can't imagine this is a query you want to run over and over again. If it is, you'd probably want to use partitioning."
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
"When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data. "
This is obvious, dropping a partition is faster than DELETE that has to go and maintain the index. I'm not talking about DELETE operations, but SELECTs, you seem to fail at reading there :-)
The word "DELETE" does not appear anywhere on that page I referred to. The examples on the page are all SELECTs. Try again.
And how do native language collations work? (and since which version are they supported per-database? are they supported per-schema? ;-)
I suspect you either know the answers to these questions or can easily look them up. Is there a particular problem you're having with them which is unsuitable for Wikipedia? Does Wikipedia not use a separate database for each language?
I got somewhat incorrect results once I used lt_LT.UTF-8 for my 'initdb' - and default collation was providing incorrect order too, as well as unique constraints were not enforcing dictionary-order rules.
postgres=# create database xx encoding 'utf8'; CREATE DATABASE postgres=# \c xx; You are now connected to database "xx". xx=# create table t1 (a varchar(255) primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE xx=# insert into t1 values ('a'); INSERT 0 1 xx=# insert into t1 values ('b'); INSERT 0 1 xx=# insert into t1 values ('ą'); INSERT 0 1 xx=# select * from t1 order by a; a
a b ą (3 rows)
You guys seem to talk about stuff you never used and never really understood. Good for you, probably much easier that way.
Sorry, I can't reproduce your error:
a=# create table t1 (a varchar(255) primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE a=# insert into t1 values ('a'); INSERT 0 1 a=# insert into t1 values ('b'); INSERT 0 1 a=# insert into t1 values ('ą'); INSERT 0 1 a=# select * from t1 order by a; a --- a ą b (3 rows)
I suspect operator error, but if you want to submit your bug to http://www.postgresql.org/support/submitbug I'm sure someone will go over it with you.