On Wed, Nov 25, 2009 at 12:39 AM, Domas Mituzas <midom.lists(a)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.