Re: PostgreSQL runs a query much slower than BDE and MySQL

Поиск
Список
Период
Сортировка
От Peter Hardman
Тема Re: PostgreSQL runs a query much slower than BDE and MySQL
Дата
Msg-id 44E43FFA.23823.5E0BDD@peter.ssbg.zetnet.co.uk
обсуждение исходный текст
Ответ на PostgreSQL runs a query much slower than BDE and MySQL  ("Peter Hardman" <peter@ssbg.zetnet.co.uk>)
Список pgsql-performance
On 17 Aug 2006 at 10:00, Mario Weilguni wrote:

> not really sure if this is right without any testdata, but isn't that what you
> want?
>
> CREATE index foo on sheep_flock (flock_no);
>
> SELECT DISTINCT on (f1.transfer_date) f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1
> WHERE f1.flock_no = '1359'
> order by f1.transfer_date desc;
>
> best regards,
> mario weilguni
>
>
Mario, Thanks for the suggestion, but this query produces the wrong answer - but
then I provided no data, nor properly explained what the data would be.
Each sheep will have multiple records, starting with one for when it's first
registered, then one for each flock it's in (eg sold into) then one for when it dies
and goes to the 'big flock in the sky'.

 So first I need to find the most recent record for each sheep and then select the
sheep who's most recent record matches the flock in question.

Your query finds all the sheep that have been in the flock in question, then selects
the first one from each set of records with the same date. So it collects data on
dead sheep, and only selects one sheep if several were bought or registered on
the same day.

Forgive me for being verbose - I want to make sure I understand it propely myself!

regards,
 --
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Beginner optimization questions, esp. regarding Tsearch2
Следующее
От: "Peter Hardman"
Дата:
Сообщение: Re: PostgreSQL runs a query much slower than BDE and MySQL