Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Pavel Stehule
Hello 2012/9/2 Sergey Koposov kopo...@ast.cam.ac.uk: Hi, I'm experiencing the case when bitmap scan is ~ 70 times slower than index scan which seems to be caused by 1) very big table 2) some hash search logic (hash_search_with_hash_value ) Here is the explain analyze of the query with

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Peter Geoghegan
On 2 September 2012 06:21, Sergey Koposov kopo...@ast.cam.ac.uk wrote: Hi, I'm experiencing the case when bitmap scan is ~ 70 times slower than index scan which seems to be caused by 1) very big table 2) some hash search logic (hash_search_with_hash_value ) Here is the explain analyze of

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
On Sun, 2 Sep 2012, Pavel Stehule wrote: statistics on data_objid_idx table are absolutly out - so planner cannot find optimal plan That doesn't have anything to do with the problem, AFAIU. First, the data table is static and was analysed. Second, the query in question is the join, and

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
Thanks for your comments. On Sun, 2 Sep 2012, Peter Geoghegan wrote: On 2 September 2012 06:21, Sergey Koposov kopo...@ast.cam.ac.uk wrote: I think that this kind of question is better suited to the pgsql-performance list. Granted, it was presented as a bug report (though they're generally sent

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Tom Lane
Sergey Koposov kopo...@ast.cam.ac.uk writes: On Sun, 2 Sep 2012, Peter Geoghegan wrote: One obvious red-flag from your query plans is that there is a misestimation of the row return count of a few orders of magnitude in the Bitmap Index Scan node. Did you trying performing an ANALYZE to see

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
On Sun, 2 Sep 2012, Tom Lane wrote: Sergey Koposov kopo...@ast.cam.ac.uk writes: The problem is definitely the misestimation here: - Index Scan using data_objid_idx on data d1 (cost=0.00..26603.32 rows=415080 width=40) (actual time=0.010..0.050 rows=248 loops=11456)

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Peter Geoghegan
On 2 September 2012 16:26, Sergey Koposov kopo...@ast.cam.ac.uk wrote: After looking at them, I think I understand the reason -- the number of n_distinct for crts.data is terribly wrong. In reality it should be ~ 130 millions. I already faced this problem at certain point when doing group by

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
On Sun, 2 Sep 2012, Peter Geoghegan wrote: On 2 September 2012 16:26, Sergey Koposov kopo...@ast.cam.ac.uk wrote: That's why we support altering that value with an ALTER TABLE...ALTER COLUMN DDL statement. You might at least consider increasing the statistics target for the column first

[HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-01 Thread Sergey Koposov
Hi, I'm experiencing the case when bitmap scan is ~ 70 times slower than index scan which seems to be caused by 1) very big table 2) some hash search logic (hash_search_with_hash_value ) Here is the explain analyze of the query with bitmap scans allowed: wsdb= explain analyze select * from