Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9

Thank you for the swift reply.
The test server is hardly ever vacuumed as it in general sees very
limited traffic. vacuum is only necessary if the server sees a lot of
write operations, i.e. update, delete, insert right?

What explains the different choice of query plans then?
As can be seen from the following snippets the test server decides to
use an index twice in Query 2, where as the live server decides to do a
full scan of tables with 38.5k and 5.5k records.
In Query 3 it's vice versa.
Seems strange to me...

Query 2:
------------------- Bad idea, price_tbl hold 38.5k records
Test:
-> Index Scan using aff_price_uq on price_tbl (cost=0.00..6.01 rows=1
width=4) (actual time=0.01..0.01 rows=1 loops=2838)"
Live:
-> Seq Scan on price_tbl (cost=0.00..883.48 rows=2434 width=4) (actual
time=0.86..67.25 rows=4570 loops=1)"
Filter: (affid = 8)"

------------------- Bad idea, sct2subcattype_tbl hold 5.5k records
Test:
-> Index Scan using subcat_uq on sct2subcattype_tbl (cost=0.00..79.26
rows=26 width=8) (actual time=0.01..0.17 rows=59 loops=48)
Live:
-> Seq Scan on sct2subcattype_tbl (cost=0.00..99.26 rows=5526
width=8) (actual time=0.01..30.16 rows=5526 loops=1)"

Query 3:
----------------- Bad idea, sct2lang_tbl has 8.6k records
Test:
-> Seq Scan on sct2lang_tbl (cost=0.00..150.79 rows=8679 width=8)
(actual time=0.03..10.70 rows=8679 loops=1)"
Live:
-> Index Scan using sct2lang_uq on sct2lang_tbl (cost=0.00..8.13
rows=2 width=8) (actual time=1.10..2.39 rows=2 loops=69)"

Will get a VACUUM VERBOSE of StatCon_Tbl

Cheers
Jona

PS: The query plans are extracted using pgAdmin on Windows, if you can
recommend a better cross-platform postgre client I'd be happy to try it out.

Tom Lane wrote:

>Jona <jonanews(at)oismail(dot)com> writes:
>
>
>>I'm currently experiencing problems with long query execution times.
>>What I believe makes these problems particularly interesting is the
>>difference in execution plans between our test server running PostGreSQL
>>7.3.6 and our production server running PostGreSQL 7.3.9.
>>The test server is an upgraded "home machine", a Pentium 4 with 1GB of
>>memory and IDE disk.
>>The production server is a dual CPU XEON Pentium 4 with 2GB memory and
>>SCSI disks.
>>One should expect the production server to be faster, but appearently
>>not as the outlined query plans below shows.
>>
>>
>
>I think the plans are fine; it looks to me like the production server
>has serious table-bloat or index-bloat problems, probably because of
>inadequate vacuuming. For instance compare these entries:
>
>-> Index Scan using ctp_statcon on statcon_tbl (cost=0.00..6.01 rows=1 width=4) (actual time=0.05..0.31 rows=39 loops=4)
> Index Cond: ((statcon_tbl.sctid = "outer".sctid) AND (statcon_tbl.ctpid = 1))
>
>-> Index Scan using ctp_statcon on statcon_tbl (cost=0.00..20.40 rows=5 width=4) (actual time=27.97..171.84 rows=39 loops=4)
> Index Cond: ((statcon_tbl.sctid = "outer".sctid) AND (statcon_tbl.ctpid = 1))
>
>Appears to be exactly the same task ... but the test server spent
>1.24 msec total while the production server spent 687.36 msec total.
>That's more than half of your problem right there. Some of the other
>scans seem a lot slower on the production machine too.
>
>
>
>>1) How come the query plans between the 2 servers are different?
>>
>>
>
>The production server's rowcount estimates are pretty good, the test
>server's are not. How long since you vacuumed/analyzed the test server?
>
>It'd be interesting to see the output of "vacuum verbose statcon_tbl"
>on both servers ...
>
> regards, tom lane
>
>PS: if you post any more query plans, please try to use software that
>doesn't mangle the formatting so horribly ...
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>