Re: Order by and index
От | Mladen Gogala |
---|---|
Тема | Re: Order by and index |
Дата | |
Msg-id | 4C79E861.1070606@vmsinfo.com обсуждение исходный текст |
Ответ на | Re: Order by and index (Josh Kupershmidt <schmiddy@gmail.com>) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: