Performance problem with pg8.0 - Mailing list pgsql-performance
| From | Jeroen van Iddekinge |
|---|---|
| Subject | Performance problem with pg8.0 |
| Date | |
| Msg-id | 436E453F.3060606@lycos.com Whole thread Raw |
| Responses |
Re: Performance problem with pg8.0
|
| List | pgsql-performance |
Hello,
I have some strange performance problems with quering a table.It has
5282864, rows and contains the following columns : id
,no,id_words,position,senpos and sentence all are integer non null.
Index on :
* no
* no,id_words
* id_words
* senpos, sentence, "no")
* d=primary key
"select count(1) from words_in_text" takes 9 seconds to compleet.
The query 'select * from words_in_text' takes a verry long time to
return the first record (more that 2 minutes) why?
Also the following query behaves strange.
select * from words_in_text where no <100 order by no;
explain shows that pg is using sequence scan. When i turn of sequence
scan, index scan is used and is faster. I have a 'Explain verbose
analyze' of this query is at the end of the mail.
The number of estimated rows is wrong, so I did 'set statistics 1000' on
column no. After this the estimated number of rows was ok, but pg still
was using seq scan.
Can anyone explain why pg is using sequence and not index scan?
The computer is a dell desktop with 768Mb ram. Database on the same
machine. I have analyze and vacuum all tables.
Database is 8.0.
Thanks
Jeroen
With enable_seqscan=true
{SORT
:startup_cost 138632.19
:total_cost 139441.07
:plan_rows 323552
:plan_width 24
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname id
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname no
:ressortgroupref 1
:resorigtbl 1677903
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname id_words
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname position
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname senpos
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname sentence
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 104880.80
:plan_rows 323552
:plan_width 24
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname id
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname no
:ressortgroupref 1
:resorigtbl 1677903
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname id_words
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname position
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname senpos
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname sentence
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
)
:qual (
{OPEXPR
:opno 97
:opfuncid 66
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 100 0 0 0 ]
}
)
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:scanrelid 1
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:numCols 1
:sortColIdx 2
:sortOperators 97
}
Sort (cost=138632.19..139441.07 rows=323552 width=24) (actual
time=7677.614..8479.980 rows=194141 loops=1)
Sort Key: "no"
-> Seq Scan on words_in_text (cost=0.00..104880.80 rows=323552
width=24) (actual time=187.118..5761.991 rows=194141 lo
ops=1)
Filter: ("no" < 100)
Total runtime: 9225.382 ms
With enable_seqscan=false
{INDEXSCAN
:startup_cost 0.00
:total_cost 606313.33
:plan_rows 323552
:plan_width 24
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname id
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname no
:ressortgroupref 1
:resorigtbl 1677903
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname id_words
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname position
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname senpos
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname sentence
:ressortgroupref 0
:resorigtbl 1677903
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:nParamExec 0
:scanrelid 1
:indxid (o 1677911)
:indxqual ((
{OPEXPR
:opno 97
:opfuncid 66
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 100 0 0 0 ]
}
)
}
))
:indxqualorig ((
{OPEXPR
:opno 97
:opfuncid 66
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 100 0 0 0 ]
}
)
}
))
:indxstrategy ((i 1))
:indxsubtype ((o 0))
:indxlossy ((i 0))
:indxorderdir 1
}
Index Scan using ind_words_in_text_1 on words_in_text
(cost=0.00..606313.33 rows=323552 width=24) (actual time=0.208..100
0.085 rows=194141 loops=1)
Index Cond: ("no" < 100)
Total runtime: 1733.601 ms
pgsql-performance by date: