Re: Help with performance and explain.

Oisin Glynn wrote:
> I have an issue with a select returning very slowly approx 198
> seconds. I took a backup of this DB and restored it on another system
> and it is returning in 28 seconds.
>
> Windows 2000 PG Version 8.0.3 Data is inserted into the table row by
> row.
> Table has index on service
> explain SELECT
> callrecord."service", callrecord."timequeuing",
> callrecord."timeconversation", callrecord."timeoffering",
> callrecord."calltype", callrecord."application"
> FROM
> "public"."callrecord" callrecord
> ORDER BY
> callrecord."service" ASC
>
> "Sort (cost=284032.83..284322.17 rows=115734 width=46)"
> " Sort Key: service"
> " -> Seq Scan on callrecord (cost=0.00..270144.34 rows=115734
> width=46)"
>
>
> Windows 2000 Version 8.0.1 DB created from backup of other server.
> explain SELECT
> callrecord."service", callrecord."timequeuing",
> callrecord."timeconversation", callrecord."timeoffering",
> callrecord."calltype", callrecord."application"
> FROM
> "public"."callrecord" callrecord
> ORDER BY
> callrecord."service" ASC
> "Sort (cost=17465.33..17754.65 rows=115729 width=46)"
> " Sort Key: service"
> " -> Seq Scan on callrecord (cost=0.00..3577.29 rows=115729 width=46)"
>
> Any help would be greatly appreciated.
>
> Oisin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

I decided to Import a dump into the same server that was displaying the
slow response and it was fine on the second database.... Explain below.
So I am even more puzzled but I guess my immediate issue is over. I
had vacuumed and analyzed this pre dumping to no avail on the initial db.

"Sort (cost=31284.82..31576.38 rows=116622 width=234)"
" Sort Key: service"
" -> Seq Scan on callrecord (cost=0.00..3605.22 rows=116622 width=234)"

Oisin