Re: Slow performance with no apparent reason

Ok, those figures look like you've mever run ANALYZE on that database at
all, given you keep getting the default values. EXPLAIN ANALYZE would have
given the actual number of matching rows.

Given that, the plans are probably extremely suboptimal. Also, do you have
(unique) indexes on the columns that need it.

So the EXPLAIN ANALYZE output after running ANALYZE over your database would
be the next step.

Hope this helps,

On Sun, Oct 26, 2003 at 01:26:22AM +0300, Yonatan Goraly wrote:
> I guess my first message was not accurate, since t1 is a view, that
> includes t2.
>
> Attached are the real queries with their corresponding plans, the first
> one takes 10.8 sec to execute, the second one takes 0.6 sec.
>
> To simplify, I expanded the view, so the attached query refers to tables
> only.
>
> Martijn van Oosterhout wrote:
>
> >Please supply EXPLAIN ANALYZE output.
> >
> >On Sun, Oct 26, 2003 at 12:25:37AM +0300, Yonatan Goraly wrote:
> >
> >
> >>I am in the process of adding PostgreSQL support for an application, in
> >>addition to Oracle and MS SQL.
> >>I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III
> >>board.
> >>
> >>I have a query that generally looks like this:
> >>
> >>SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND t2.p='string'
> >>AND t2.q=1
> >>
> >>This query is strikingly slow (about 100 sec when both t1 and t2 has
> >>about 1,200 records, compare with less than 4 sec with MS SQL and Oracle)
> >>
> >>The strange thing is that if I remove one of the last 2 conditions
> >>(doesn't matter which one), I get the same performance like with the
> >>other databases.
> >>Since in this particular case both conditions ( t2.p='string', t2.q=1)
> >>are not required, I can't understand why having both turns the query so
> >>slow.
> >>A query on table t2 alone is fast with or without the 2 conditions.
> >>
> >>I tired several alternatives, this one works pretty well:
> >>
> >>SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND
> >> EXISTS (
> >> SELECT * FROM t2 t2a WHERE t2a.p='string' AND t2a.q=1 AND
> >>t2a.y=t2.y )
> >>
> >>Since the first query is simpler than the second, it seems to me like a
> >>bug.
> >>
> >>Please advise
> >>
> >>Yonatan
> >>
> >>
> >
> >
> >

> ------------------------------------------------------------------------------------------------------------
> slow query(10 sec):
>
> select ent.ID,ent.TYPE,ent.STATUS,ent.NAME
> from (select
> e.ID, e.TYPE, e.STATUS, e.NAME
> from
> ENT_PROJECT e,
> (select h.*,
> CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME ||
> CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME ||
> CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME ||
> CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME ||
> CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME ||
> CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END END END END as PATH
> from COMN_ATTR_HIERARCH h
> join ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID
> join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID
> join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID
> join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID
> join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID
> join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID
> ) path
> where e.STATUS!=cast(-1 as numeric)
> and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier
> where hier.NODE_ID=ent.ID and hier.HIERARCHY_ID='IMPLEMENTATION' and hier.DOMAIN=1
>
>
> ------------------------------------------------------------------------------------------------------------
> QUERY PLAN
> Nested Loop (cost=1808.05..1955.27 rows=14 width=660)
> Join Filter: ("outer".id = "inner".node_id)
> -> Nested Loop (cost=0.00..10.82 rows=1 width=244)
> -> Index Scan using idx_hierarch_hierarch_id on comn_attr_hierarch hier (cost=0.00..5.98 rows=1 width=32)
> Index Cond: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND ("domain" = 1::numeric))
> -> Index Scan using pk_ent_project on ent_project e (cost=0.00..4.83 rows=1 width=212)
> Index Cond: ("outer".node_id = e.id)
> Filter: (status <> -1::numeric)
> -> Materialize (cost=1910.33..1910.33 rows=2730 width=416)
> -> Merge Join (cost=1808.05..1910.33 rows=2730 width=416)
> Merge Cond: ("outer".id = "inner".folder_id_6)
> -> Index Scan using pk_ent_folder on ent_folder f6 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=1808.05..1814.88 rows=2730 width=384)
> Sort Key: h.folder_id_6
> -> Merge Join (cost=1275.45..1377.73 rows=2730 width=384)
> Merge Cond: ("outer".id = "inner".folder_id_5)
> -> Index Scan using pk_ent_folder on ent_folder f5 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=1275.45..1282.28 rows=2730 width=352)
> Sort Key: h.folder_id_5
> -> Merge Join (cost=1017.37..1119.64 rows=2730 width=352)
> Merge Cond: ("outer".id = "inner".folder_id_4)
> -> Index Scan using pk_ent_folder on ent_folder f4 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=1017.37..1024.19 rows=2730 width=320)
> Sort Key: h.folder_id_4
> -> Merge Join (cost=759.28..861.56 rows=2730 width=320)
> Merge Cond: ("outer".id = "inner".folder_id_3)
> -> Index Scan using pk_ent_folder on ent_folder f3 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=759.28..766.11 rows=2730 width=288)
> Sort Key: h.folder_id_3
> -> Merge Join (cost=501.20..603.47 rows=2730 width=288)
> Merge Cond: ("outer".id = "inner".folder_id_2)
> -> Index Scan using pk_ent_folder on ent_folder f2 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=501.20..508.02 rows=2730 width=256)
> Sort Key: h.folder_id_2
> -> Merge Join (cost=243.11..345.39 rows=2730 width=256)
> Merge Cond: ("outer".id = "inner".folder_id_1)
> -> Index Scan using pk_ent_folder on ent_folder f1 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=243.11..249.94 rows=2730 width=224)
> Sort Key: h.folder_id_1
> -> Seq Scan on comn_attr_hierarch h (cost=0.00..87.30 rows=2730 width=224)
>
>
> ------------------------------------------------------------------------------------------------------------
> Fast query (.6 sec):
>
> select ent.ID,ent.TYPE,ent.STATUS,ent.NAME
> from (select
> e.ID, e.TYPE, e.STATUS, e.NAME
> from
> ENT_PROJECT e,
> (select h.*,
> CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME ||
> CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME ||
> CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME ||
> CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME ||
> CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME ||
> CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END END END END as PATH
> from COMN_ATTR_HIERARCH h
> join ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID
> join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID
> join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID
> join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID
> join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID
> join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID
> ) path
> where e.STATUS!=cast(-1 as numeric)
> and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier
> where hier.NODE_ID=ent.ID and exists(
> select * from COMN_ATTR_HIERARCH h2 where h2.HIERARCHY_ID='IMPLEMENTATION' and h2.DOMAIN=1 and h2.NODE_ID=hier.NODE_ID
> and h2.HIERARCHY_ID=hier.HIERARCHY_ID and h2.DOMAIN=hier.DOMAIN)
>
>
> ------------------------------------------------------------------------------------------------------------
> QUERY PLAN
> Merge Join (cost=16145.60..16289.84 rows=18539 width=660)
> Merge Cond: ("outer".id = "inner".node_id)
> -> Merge Join (cost=13782.29..13863.08 rows=1358 width=244)
> Merge Cond: ("outer".id = "inner".node_id)
> -> Index Scan using pk_ent_project on ent_project e (cost=0.00..54.50 rows=995 width=212)
> Filter: (status <> -1::numeric)
> -> Sort (cost=13782.29..13785.70 rows=1365 width=32)
> Sort Key: hier.node_id
> -> Seq Scan on comn_attr_hierarch hier (cost=0.00..13711.21 rows=1365 width=32)
> Filter: (subplan)
> SubPlan
> -> Index Scan using pk_comn_attr_hierarch on comn_attr_hierarch h2 (cost=0.00..4.99 rows=1 width=316)
> Index Cond: (("domain" = 1::numeric) AND ("domain" = $2) AND (node_id = $0))
> Filter: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND (hierarchy_id = $1))
> -> Sort (cost=2363.32..2370.14 rows=2730 width=416)
> Sort Key: h.node_id
> -> Merge Join (cost=1808.05..1910.33 rows=2730 width=416)
> Merge Cond: ("outer".id = "inner".folder_id_6)
> -> Index Scan using pk_ent_folder on ent_folder f6 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=1808.05..1814.88 rows=2730 width=384)
> Sort Key: h.folder_id_6
> -> Merge Join (cost=1275.45..1377.73 rows=2730 width=384)
> Merge Cond: ("outer".id = "inner".folder_id_5)
> -> Index Scan using pk_ent_folder on ent_folder f5 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=1275.45..1282.28 rows=2730 width=352)
> Sort Key: h.folder_id_5
> -> Merge Join (cost=1017.37..1119.64 rows=2730 width=352)
> Merge Cond: ("outer".id = "inner".folder_id_4)
> -> Index Scan using pk_ent_folder on ent_folder f4 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=1017.37..1024.19 rows=2730 width=320)
> Sort Key: h.folder_id_4
> -> Merge Join (cost=759.28..861.56 rows=2730 width=320)
> Merge Cond: ("outer".id = "inner".folder_id_3)
> -> Index Scan using pk_ent_folder on ent_folder f3 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=759.28..766.11 rows=2730 width=288)
> Sort Key: h.folder_id_3
> -> Merge Join (cost=501.20..603.47 rows=2730 width=288)
> Merge Cond: ("outer".id = "inner".folder_id_2)
> -> Index Scan using pk_ent_folder on ent_folder f2 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=501.20..508.02 rows=2730 width=256)
> Sort Key: h.folder_id_2
> -> Merge Join (cost=243.11..345.39 rows=2730 width=256)
> Merge Cond: ("outer".id = "inner".folder_id_1)
> -> Index Scan using pk_ent_folder on ent_folder f1 (cost=0.00..52.00 rows=1000 width=32)
> -> Sort (cost=243.11..249.94 rows=2730 width=224)
> Sort Key: h.folder_id_1
> -> Seq Scan on comn_attr_hierarch h (cost=0.00..87.30 rows=2730 width=224)
>
> ------------------------------------------------------------------------------------------------------------

>
> ---------------------------(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

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato