On Mon, Mar 3, 2014 at 10:12 PM, Damon Snyder wrote:
>
>> Um... I think your problem is a misuse of CTE. Your CTE is building an
> intermediate of several thousands of rows only to select a dozen
> afterwards. You may want to consider a view or subquery, though I'm
> not sure pg will be able to op
Hi Claudio,
See my comments inline below.
> Um... I think your problem is a misuse of CTE. Your CTE is building an
intermediate of several thousands of rows only to select a dozen
afterwards. You may want to consider a view or subquery, though I'm
not sure pg will be able to optimize much given yo
On Mon, Mar 3, 2014 at 9:17 PM, acanada wrote:
> Hello,
>
> Thankyou for your answer.
> I have made more changes than a simple re-indexing recently. I have moved
> the sorting field to the table in order to avoid the join clause. Now the
> schema is very simple. The query only implies one table:
Um... I think your problem is a misuse of CTE. Your CTE is building an
intermediate of several thousands of rows only to select a dozen
afterwards. You may want to consider a view or subquery, though I'm
not sure pg will be able to optimize much given your use of window
functions, which forces a ma
Hello,
I have two versions of essentially the same query; one using nested joins,
the other using subselects. The version using the subselect is roughly an
order of magnitude faster (~70ms on my box and data vs ~900ms for the
nested joins). Of course the obvious answer here is just to use the fast
Hi Claudio,
Thanks for responding. Here is the explain (http://explain.depesz.com/s/W3W)
for the ordering by meta container starting on line 192 (
https://gist.github.com/drsnyder/9277054#file-object-ordering-setup-sql-L192
).
Here is the explain (http://explain.depesz.com/s/d1O) for the ordering
> I have simplified the query and added the last advise that you told me:
>
> Query:
>
> explain analyze select * from (select * from
entity_compounddict2document where name='ranitidine') as a order by
a.hepval;
>
Do you need full result?
If you need just top-n rows, then index on
entity_compoun
