1. Try it (EXPLAIN ANALYZE). If an index's first column is a column from
ORDER BY, and query planner sees that it is more efficient to scan a table
in the order specified by ORDER BY, Postgres will use that index. And if it
estimates that it is more efficient to filter rows first and then - sort
them, it uses another index. You have to use EXPLAIN ANALYZE on your real
data to see what index is most efficient (in my practice theoretical
predictions are not effective).

2. In Postgres you may create an index on a axpresseion, e.g. index on
substr(column_name, 0, 100). But, if you want to use this index, you have to
search using substr(column_name, 0, 100) =3D 'abcd', not by column_name =3D
'abcd'


On 8/14/07, Phoenix Kiula <phoenix.kiula at gmail.com> wrote:
>
> I have a table with ten columns. My queries basically one column as
> the first WHERE condition, so an index on that column is certain. But
> the columns after that one vary depending on end-user's choice (this
> is a reporting application) and so does the sorting order.
>
> In MySQL world, I had sort_buffer in the config file, and I made a
> compound index with the columns most often used in these types of
> queries. So my index looked like:
>
>   INDEX idx_trades(id, t_id, c_id, s_id,  t_brief, created_on);
>
> This has five columns in it. While reading the pgsql documentation, I
> gather than anything beyond three columns offers diminishing benefits.
>
> My queries will look like these:
>
>    SELECT * from trades where id =3D 99999
>    and c_id =3D 9999
>    ORDER by s_id;
>
>    SELECT * from trades where id =3D 99999
>    and s_id =3D 99990
>    ORDER by created_on desc ;
>
>    SELECT * from trades where id =3D 99999
>    and s_id =3D 99990
>    and t_brief ~* 'more|than|one|word'
>    ORDER by created_on desc ;
>
> So my question: how does PGSQL optimize its sorts? If I were to index
> merely the columns that are most commonly used in the reporting WHERE
> clause, would that be ok? Some ofthese columns may be "TEXT" type --
> how should I include these in the index (in MySQL, I included only the
> first 100 words in the index).
>
> TIA!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070815/=
e96d7314/attachment.htm