[GENERAL] parameter substitution problem in jdbc driver? (8.1)
When a query of this form: /* from xxx where yyy = ? */ select a,b,c,d from xxx where yyy = ? is sent to the jdbc driver (as a prepared statement), it complains that parameter 2 is not set. This is a query from hibernate3.1, with hibernate's generate comments in sql option turned on. I'm not really sure who is at fault here, or if this is one of those unspecified things, but I would assume that parameter substitution should not apply to comments? Looking at the prepared statement in the debugger, it did indeed try to do parameter substitution inside the comment. Is the problem in the JDBC driver, in Hibernate, or in the JDBC spec? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] enable_sort optimization problem
tom lane wrote:
Why does it think that only 159 of the 132245 rows in outages will have
join partners in ipinterface? The actual results look like they all do.
It might be worth looking at the pg_stats rows for the join columns to
see if there's something odd about the statistics.
Here are the pg_stats (as of today, I haven't done any analyzes or
vacuums since the night of my first posting) for outages and ipinterface
(I've obscured some addresses in the ipiddr row, and removed iphostname):
schemaname | tablename | attname | null_frac | avg_width |
n_distinct
|
most_common_vals
|
most_common_freqs
|
histogram_bounds
| correlation
+---++-+---++---+---+-+-
public | outages | outageid | 0 | 4
| -1
|
|
|
{201,14775,27621,39600,53231,66043,79629,92779,105267,119744,134644}
|0.390484
public | outages | svclosteventid | 0 | 4 |
-0.945011 |
{280277,356772}
|
{0.00067,0.00067}
|
{17842,54830,81745,107030,135793,163240,191819,219223,268449,309128,371234}
|0.390484
public | outages | svcregainedeventid | 0.00033 | 4 |
-0.958031 |
{280279}
|
{0.00067}
|
{17844,54856,81787,107063,135821,163381,191856,219405,268450,309572,371494}
|0.390818
public | outages | nodeid | 0 | 4
|396 |
{962,397,191,185,377,139,237,378,1295,231}
|
{0.017,0.0097,0.0087,0.0083,0.0083,0.0077,0.0077,0.0077,0.0077,0.007}
|
{3,158,178,206,236,258,293,316,358,395,1452}
| -0.0325868
public | outages | ipaddr | 0 |16
|396 |
{208,172.22.0.158,172.20.0.237,172.20.0.231,172.22.35.56,172.17.2.5,172.20.0.180,172.21.240.91,172.23.181.16,172.21.240.93}
|
[GENERAL] enable_sort optimization problem
(8.0.1 on debian/linux 2.6.11 kernel) I have noticed that if I set enable_sort=false in the .conf file, my queries are running faster. I had a query which if I did a limit 20, ran in 6 milliseconds, but if I changed it to limit 21, it took around 19 seconds (or 19000 milliseconds). It also took longer if I did limit 19 offset 2. (I don't know what it is about the 21st record). In any case, I noticed that in the analysis, the long version was doing a sort and the quick version was not, so I tried the enable_sort=false setting, and now things are generally running faster. I HAVE done analyzes, and vacuums, and vacuum analyzes. In prior experimenting with this, there were even some seq_scans, which turned into index_scans when I set enable_seqscan=false, and became moderately faster. I am using 8.0.1, and below are the two query plans, first the enable_sort=true version, then the enable_sort=false version, note the VAST difference in speed. What is the problem, and how can I convince the query optimizer to do the right thing (short of enable_sort=false)? from the config file: # - Planner Cost Constants - #effective_cache_size = 1000# typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) With enable_sort=true: (the default) opennms=# explain analyze SELECT OUTAGES.*, NODE.NODELABEL, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES JOIN NODE USING(NODEID) JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID ORDER BY OUTAGEID DESC LIMIT 29 OFFSET 2; QUERY PLAN Limit (cost=127422.75..127422.82 rows=29 width=126) (actual time=40858.260..40858.608 rows=29 loops=1) - Sort (cost=127422.74..127423.14 rows=161 width=126) (actual time=40858.230..40858.367 rows=31 loops=1) Sort Key: outages.outageid - Nested Loop Left Join (cost=1047.62..127416.84 rows=161 width=126) (actual time=2448.671..38122.920 rows=134579 loops=1) - Hash Left Join (cost=1047.62..126533.18 rows=161 width=102) (actual time=2448.616..23963.962 rows=132250 loops=1) Hash Cond: (outer.serviceid = inner.serviceid) - Hash Join (cost=1044.17..126527.32 rows=161 width=92) (actual time=1996.055..21178.268 rows=132250 loops=1) Hash Cond: ((outer.nodeid = inner.nodeid) AND ((outer.ipaddr)::text = (inner.ipaddr)::text)) - Index Scan using outages_serviceid_idx on outages (cost=0.00..123497.86 rows=132245 width=52) (actual time=81.413..15330.873 rows=132250 loops=1) - Hash (cost=1037.31..1037.31 rows=1372 width=64) (actual time=1914.558..1914.558 rows=0 loops=1) - Merge Join (cost=0.00..1037.31 rows=1372 width=64) (actual time=150.436..1906.441 rows=1372 loops=1) Merge Cond: (outer.nodeid = inner.nodeid) - Index Scan using pk_nodeid on node (cost=0.00..229.55 rows=1043 width=24) (actual time=0.022..623.197 rows=1043 loops=1) - Index Scan using ipinterface_nodeid_idx on ipinterface (cost=0.00..788.01 rows=1372 width=40) (actual time=150.369..1259.366 rows=1372 loops=1) - Hash (cost=3.36..3.36 rows=33 width=14) (actual time=452.519..452.519 rows=0 loops=1) - Index Scan using pk_serviceid on service (cost=0.00..3.36 rows=33 width=14) (actual time=452.193..452.369 rows=33 loops=1) - Index Scan using notifications_eventid_idx on notifications (cost=0.00..5.46 rows=2 width=28) (actual time=0.078..0.079 rows=0 loops=132250) Index Cond: (outer.svclosteventid = notifications.eventid) Total runtime: 40868.130 ms (19 rows) And the enable_sort=false version: opennms=# explain analyze SELECT OUTAGES.*, NODE.NODELABEL, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES JOIN NODE USING(NODEID) JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID ORDER BY OUTAGEID DESC LIMIT 29 OFFSET 2;
