Re: [GENERAL] General query optimization howto
Miroslav ¦ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. I doubt that there is a generic documentation on SQL optimization, because this heavily depends on the particular implementation that you are using. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. You should probably start with the Performance Tips chapter in the PostgreSQL documentation. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] General query optimization howto
On Sun, 2005-03-13 at 02:34 +0100, Miroslav ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. EXPLAIN ANALYZE is even more useful. start with looking for inconsistencies between row estimates and actual row counts. these could mean that you need to ANALYZE, or increase statistics for some columns. also look for expensive sequential scans where you would expect an index scan. this may be due to missing indexes, imcompatible column types, lack of ANALYZE, or insufficient statistics. browse through the archives of the pgsql-performance list, to get a feel of typical problems, and to read illuminating responses from regulars. if you still are having problems, make the simplest test case you can, and post an EXPLAIN ANALYZE to pgsql-performance, along with relevant data, such as table definitions, typical data distributions and postgres version. gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] General query optimization howto
Bruce Momjian wrote: Have you read the FAQ? Yes, but I have found only some useful information saying when indexes are not used. Miroslav ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] General query optimization howto
Peter Eisentraut wrote: Miroslav ¦ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. I doubt that there is a generic documentation on SQL optimization, because this heavily depends on the particular implementation that you are using. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. You should probably start with the Performance Tips chapter in the PostgreSQL documentation. I've read that one. Sure it is good starting point but for a newbie (= me) it's not enough :-( Miroslav ¦ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(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
