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 afaik the estimation of the number of rows is known to be incorrect, in the case of column correlation. Third, according at least to my understanding in the fully cached regime bitmap scan should not take two orders of magnitude more CPU time than index scan.

        Sergey

Regard

Pavel Stehule

                           Index Cond: (objid = t.objid)
 Total runtime: 66622.026 ms
(11 rows)

Here is the output when bitmap scans are disabled:
QUERY PLAN
                                                                     QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..329631941.65 rows=10000 width=68) (actual
time=0.082..906.876 rows=10000 loops=1)
   ->  Nested Loop  (cost=0.00..4979486036.95 rows=151062 width=68) (actual
time=0.081..905.683 rows=10000 loops=1)
         Join Filter: (t.mjd = d1.mjd)
         ->  Seq Scan on test t  (cost=0.00..2632.77 rows=151677 width=28)
(actual time=0.009..1.679 rows=11456 loops=1)
         ->  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)
               Index Cond: (objid = t.objid)
 Total runtime: 907.462 ms

When the bitmap scans are enabled the "prof" of postgres shows
    47.10%  postmaster  postgres           [.] hash_search_with_hash_value
            |
            --- hash_search_with_hash_value

    11.06%  postmaster  postgres           [.] hash_seq_search
            |
            --- hash_seq_search

     6.95%  postmaster  postgres           [.] hash_any
            |
            --- hash_any

     5.17%  postmaster  postgres           [.] _bt_checkkeys
            |
            --- _bt_checkkeys

     4.07%  postmaster  postgres           [.] tbm_add_tuples
            |
            --- tbm_add_tuples

     3.41%  postmaster  postgres           [.] hash_search
            |
            --- hash_search


And the last note is that the crts.data table which is being bitmap scanned
is a 1.1Tb table with ~ 20e9 rows. My feeling is that the bitmap index scan
code
is somehow unprepared to combine two bitmaps for such a big table, and this
leads to the terrible performance.

Regards,
        Sergey

PS Here are the schemas of the tables, just in case:
wsdb=> \d test
          Table "koposov.test"
 Column  |       Type       | Modifiers
---------+------------------+-----------
 mjd     | double precision |
 fieldid | bigint           |
 intmag  | integer          |
 objid   | bigint           |

wsdb=> \d crts.data
           Table "crts.data"
 Column |       Type       | Modifiers
--------+------------------+-----------
 objid  | bigint           |
 mjd    | double precision |
 mag    | real             |
 emag   | real             |
 ra     | double precision |
 dec    | double precision |
Indexes:
    "data_mjd_idx" btree (mjd) WITH (fillfactor=100)
    "data_objid_idx" btree (objid) WITH (fillfactor=100)
    "data_q3c_ang2ipix_idx" btree (q3c_ang2ipix(ra, "dec")) WITH
(fillfactor=100)

PPS shared_buffers=10GB, work_mem=1GB
All the test shown here were don in fully cached regime.

PPS I can believe that what I'm seeing is a feature, not a bug of bitmap
scans,
and I can live with disabling them, but I still thought it's worth
reporting.


*****************************************************
Sergey E. Koposov, PhD, Research Associate
Institute of Astronomy, University of Cambridge
Madingley road, CB3 0HA, Cambridge, UK
Tel: +44-1223-337-551


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


*****************************************************
Sergey E. Koposov, PhD, Research Associate
Institute of Astronomy, University of Cambridge
Madingley road, CB3 0HA, Cambridge, UK
Tel: +44-1223-337-551 Web: http://www.ast.cam.ac.uk/~koposov/


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to