On Tue, Nov 6, 2012 at 12:57 PM, Rodrigo Rosenfeld Rosas
<rr.rosas@gmail.com> wrote:
> I would strongly consider investigation of hstore type along with
> gist/gin index.
> select * from company_transaction where contract_attributes @>
> 'State=>Delaware, Paid=Y';
> etc
>
>
> I'm not very familiar with hstore yet but this was one of the reasons I
> wanted to migrate to PG 9.2 but I won't be able to migrate the application
> quickly to use hstore.
sure -- it's a major change. note though that 9.1 hstore has
everything you need.
> Also, I'm not sure if hstore allows us to be as flexible as we currently are
> (c1 and (c2 or c3 and not (c4 and c5))). c == condition
your not gated from that functionality, although making complicated
expressions might require some thought and defeat some or all of GIST
optimization. that said, nothing is keeping you from doing:
where fields @> 'c1=>true, c2=>45' and not (fields @> 'c3=>false, c4=>xyz');
range searches would completely bypass GIST. so that:
select * from foo where attributes -> 'somekey' between 'value1' and 'value2';
would work but would be brute force. Still, with a little bit of
though, you should be able to optimize most common cases and when it
boils down to straight filter (a and b and c) you'll get an orders of
magnitude faster query.
>> Barring that, I would then consider complete elimination of integer
> proxies for your variables. They make your query virtually impossible
> to read/write, and they don't help.
>
> I'm not sure if I understood what you're talking about. The template is
> dynamic and contains lots of information for each field, like type (number,
> percent, string, date, etc), parent_id (auto-referencing), aggregator_id
> (also auto-referencing) and several other columns. But the values associate
> the field id (type_id) and the transaction id in a unique way (see unique
> index in my first message of the thread). Then I need different tables to
> store the actual value because we're using SQL instead of MongoDB or
> something else. The table that stores the value depend on the field type.
Well, that's probably a mistake. It's probably better to have a
single table with a text field (which is basically a variant) and a
'type' column storing the type of it if you need special handling down
the line. One thing I'm sure of is that abstracting type behind
type_id is doing nothing but creating needless extra work. You're
doing all kinds of acrobatics to fight the schema by hiding it under
various layers of abstraction.
merlin