Re: [PERFORM] PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
On 03/22/2010 03:21 PM, Tom Lane wrote: The fundamental reason why you're getting a bad plan choice is the factor-of-100 estimation error here. I'm not sure you can do a whole lot about that without rethinking the query --- in particular I would suggest trying to get rid of the non-constant range bounds. You're apparently already plugging in an external variable for the date, so maybe you could handle the time of day similarly instead of joining to sysstrings for it. Tom Peter, I thought you might like to know the outcome of this. I was able to get the 8.0 and the 8.2 planner to correctly run the query. There were 2 issues. As Tom pointed out the the 'systrings' lookup seems to be the main culprit. Which makes sense. How can the planner know how to run the query when it doesn't know approximately what it will bracket the until the query has started? The other part of the solution is bit concerning. I had to do a 'dump and load' (and vacuum analyze) to get the planner to work correctly even after I rewrote the query. FYI I had run 'VACUUM ANALYZE' (and sometimes 'REINDEX TABLE x') between each test. -- Christian Brink -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] PostgreSQL upgraded to 8.2 but forcing index scan on query produces faster
sysstrings_pkey on
sysstrings (cost=0.00..8.27 rows=1 width=182) (actual time=0.039..0.040
rows=1 loops=1)
Index Cond: (id = 'net/Console/Employee/Day
End Time'::text)
- Index Scan using sales_tranzdate_index on
sales s (cost=0.01..1846.40 rows=22477 width=12) (actual
time=0.410..1.187 rows=225 loops=1)
Index Cond: ((s.tranzdate =
('2010-02-15'::date + (sysstrings.data)::time without time zone)) AND
(s.tranzdate ('2010-02-16'::date + (sysstrings.data)::time without
time zone)))
Filter: ((NOT void) AND (NOT suspended))
Total runtime: 2206.706 ms
(13 rows)
postream= \d salesitems;
Table public.salesitems
Column| Type | Modifiers
--+--+
id | integer | not null
lineno | smallint | not null
plu | character varying(35)|
qty | numeric(8,3) | not null
amt | numeric(10,2)|
last_updated | timestamp with time zone | default now()
group1_id| character varying(64)|
group2_id| text |
group3_id| text |
void | boolean | not null default false
hash | boolean | not null default false
component| boolean | not null default false
subitem | boolean | not null default false
Indexes:
salesitems_pkey PRIMARY KEY, btree (id, lineno)
idx_si_group_id btree (group1_id)
salesitems_last_updated_index btree (last_updated)
--
Christian Brink
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Forcing index scan on query produces 16x faster
=0.077..0.085
rows=1 loops=1)
Index Cond: (id = 'net/Console/Employee/Day
End Time'::text)
- Index Scan using sales_tranzdate_index on
sales s (cost=0.01..1825.27 rows=22530 width=12) (actual
time=0.074..1.945 rows=225 loops=1)
Index Cond: ((s.tranzdate =
('2010-02-15'::date + (outer.data)::time without time zone)) AND
(s.tranzdate ('2010-02-16'::date + (outer.data)::time without time
zone)))
Filter: ((NOT void) AND (NOT suspended))
Total runtime: 5565.262 ms
(13 rows)
--
Christian Brink
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
