Re: Order by and index

Josh Kupershmidt wrote:
> Perhaps Oracle is smart enough to use indexes on "created_at" and
> "document#" together to avoid a sort entirely, but your example
> doesn't show this. Postgres should be able to use an Index Scan and
> avoid that sort step if you don't involve "created_at":
>

Josh, the problem is the fact that the Postgres table is partitioned,
while the Oracle table is not. That should explain the mysterious
"created_at" condition, absent on the Oracle side. Partitioning is the
most important reason for copying the 200GB Oracle table to Postgres.
The problem with the partitioning on the Oracle side is that it costs
money. When I query a partition directly, the index is used:

news=# explain analyze
news-# select author from moreover_documents_y2010m06
news-# order by "document#"
news-# limit 10;

QUERY
PLAN

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------
Limit (cost=0.00..1.48 rows=10 width=20) (actual time=37.458..108.098
rows=10
loops=1)
-> Index Scan using pk_moreover_documents_y2010m06 on
moreover_documents_y20
10m06 (cost=0.00..274673.53 rows=1856853 width=20) (actual
time=37.457..108.095
rows=10 loops=1)
Total runtime: 108.130 ms
(3 rows)

If you take a look at the plan I have originally posted, you will note
that the very same table is scanned, despite the fact that the table
originally specified is "moreover_documents". I believe this to be a bug
in optimizing queries to the partitioned tables. This is a "history
table", needed for reporting purposes. Open source tools like Jasper
and Pentaho, as well as the reporting software like Crystal Reports can
use Postgres to produce reports, while this monster is slowing down
everything on the Oracle side. My problem is what indexes can be used
and when, so that the reports perform better than they do now. If that
is not the case, the report users will storm my office, with tar and
feathers. Somehow, I find such prospect unappealing.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com