Hi,
I just installed 8.0 RC1 and then restored my database which was working
perfectly on 7.4.5.
I have a function that imports rows from a comma seperated file and on
7.4.5 using this same function I could get around 1000 rows every 2.5
seconds, now with 8.0 RC1 this has gone way up and it seems to be
because it is not using the indexes defined on the table.
The reason I believe this is that as the progresses the size of the
table increases and the time for every 1000 rows grows.
by the time I am up to 20000 rows it is taking over 30 seconds per 1000
rows.
I commit the transaction after every 1000 and on 7.4.5 there were no
issues.

The function in question does some selects on the table that is being
inserted and without the indexes working it ends up taking a very long time.

unfortunatly I can't supppy any of the plpgsql function code.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Fri, Dec 17, 2004 at 10:28:20PM -0600, Tony Caduto wrote:

Can you at least post the EXPLAIN ANALYZE output for a query that
you think isn't using an index?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tony Caduto <tony.caduto (AT) amsoftwaredesign (DOT) com> writes:
Have you done an ANALYZE or VACUUM ANALYZE since loading the data?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Hi Tom,
Yes I did a vacuum analyse right away. As a further test I restored
the exact same data on another 7.4.5 server and even without a vacuum
analyse the importer program worked as expected(fast).
I posted some more info including screen shots of the importer running
in verbose mode on a 7.4.5 and a 8.0rc1 server, and much more info. I
actually think I have it narrowed down to a select count(*) in my
importer function. It seems that the select count(*) is not using the
indexes that I have defined. This same process has been in production
since June 04 and the indexes have not been changed or deleted. On
monday I am going to replace the count(*) with a for select and a
counter variable and see if the slowness goes away. It really does seem
to be some issue/bug maybe or just a difference in the 8.0 engine.

Thanks,

Tony Caduto


Tom Lane wrote:



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster